every time i create a dataset batch_id and _batch_last_run columns are generating and while joining

Reply
White Belt

every time i create a dataset batch_id and _batch_last_run columns are generating and while joining

Hi,

every time i create a dataset batch_id and _batch_last_run columns are generating and while joining datasets an error is displaying bcoz of these duplicate columns present in both datasets

is there a way to create a dataset without generating these columns or how to delete it

and one more question 

is it possible to change column names after creating a dataset

thank you


Accepted Solutions
Major Blue Belt

Re: every time i create a dataset batch_id and _batch_last_run columns are generating and while join

What type of join are you doing? How are you performing it, ETL, SQL dataflow, fusion? I ask because you should be able to join with both datasets having them, they are standard in Domo and I've yet to run into it. MySQL dataflow join example below

 

Table 1: "Favorites"

  • ID
  • Favorite_Color
  • Favorite_Food
  • UserID
  • Batch_ID
  • Batch_Last_Run

 

Table 2: "Users"

  • UserID
  • Name
  • Email
  • Batch_ID
  • Batch_Last_Run

 

Despite those columns being duplicate if you wanted to join on them you could do

 

SELECT 

f.Favorite_Color

,f.Favorite_Food

,u.Name

,u.Email

FROM Users as u

LEFT JOIN Favorites as 'f' on f.UserID = u.UserID

 

 

This shouldn't cause any errors. If you did want to include both batchID and batch last run from both datasets you could just rename them in the select statement

 

SELECT 

f.Favorite_Color

,f.Favorite_Food

,f.BatchID as 'Favorites BatchID'

,f.Batch_Last_Run as 'Favorites Batch Last Run'

,u.Name

,u.Email

,u.BatchID as 'UsersBatchID'

,u.Batch_Last_Run as 'USers Batch Last Run'

FROM Users as u

LEFT JOIN Favorites as 'f' on f.UserID = u.UserID

 

 

This should also be possible in ETL by perhaps putting a select columns type of ETL block so you don't even select them prior to joining

Highlighted
Black Belt

Re: every time i create a dataset batch_id and _batch_last_run columns are generating and while join

You could also use the SQL transform option to drop the columns.  (you can only drop one column at a time though)

 

Create a transform, select the SQL option:

ALTER TABLE `table_name` DROP COLUMN `_BATCH_ID_`;

 

then drop the other batch field:

ALTER TABLE `table_name` DROP COLUMN `_BATCH_LAST_RUN_`

 

You can then use 

SELECT a.*, b.* from `table_name` a left join `table_b_name` b on a.`id`=b.`id`

 

without a duplicat column.

 

Alternatively, you can also remove the columns by clicking on the input data set and clicking on the 'x' to the right (this only works if your field names don't have a . in them):

 

1.png

 

2.png

 

 

 

 

 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________

All Replies
Major Blue Belt

Re: every time i create a dataset batch_id and _batch_last_run columns are generating and while join

What type of join are you doing? How are you performing it, ETL, SQL dataflow, fusion? I ask because you should be able to join with both datasets having them, they are standard in Domo and I've yet to run into it. MySQL dataflow join example below

 

Table 1: "Favorites"

  • ID
  • Favorite_Color
  • Favorite_Food
  • UserID
  • Batch_ID
  • Batch_Last_Run

 

Table 2: "Users"

  • UserID
  • Name
  • Email
  • Batch_ID
  • Batch_Last_Run

 

Despite those columns being duplicate if you wanted to join on them you could do

 

SELECT 

f.Favorite_Color

,f.Favorite_Food

,u.Name

,u.Email

FROM Users as u

LEFT JOIN Favorites as 'f' on f.UserID = u.UserID

 

 

This shouldn't cause any errors. If you did want to include both batchID and batch last run from both datasets you could just rename them in the select statement

 

SELECT 

f.Favorite_Color

,f.Favorite_Food

,f.BatchID as 'Favorites BatchID'

,f.Batch_Last_Run as 'Favorites Batch Last Run'

,u.Name

,u.Email

,u.BatchID as 'UsersBatchID'

,u.Batch_Last_Run as 'USers Batch Last Run'

FROM Users as u

LEFT JOIN Favorites as 'f' on f.UserID = u.UserID

 

 

This should also be possible in ETL by perhaps putting a select columns type of ETL block so you don't even select them prior to joining

Highlighted
Black Belt

Re: every time i create a dataset batch_id and _batch_last_run columns are generating and while join

You could also use the SQL transform option to drop the columns.  (you can only drop one column at a time though)

 

Create a transform, select the SQL option:

ALTER TABLE `table_name` DROP COLUMN `_BATCH_ID_`;

 

then drop the other batch field:

ALTER TABLE `table_name` DROP COLUMN `_BATCH_LAST_RUN_`

 

You can then use 

SELECT a.*, b.* from `table_name` a left join `table_b_name` b on a.`id`=b.`id`

 

without a duplicat column.

 

Alternatively, you can also remove the columns by clicking on the input data set and clicking on the 'x' to the right (this only works if your field names don't have a . in them):

 

1.png

 

2.png

 

 

 

 

 


______________________________________________________________________________________________
“There is a superhero in all of us, we just need the courage to put on the cape.” -Superman
______________________________________________________________________________________________
Announcements
Dojo Day is Coming! Mark your calendars for May 30th 0900-1700 MST Click here for more information