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
Best Answers
-
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
- 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
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**2 -
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):
3
Answers
-
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
- 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
**Make sure to like any users posts that helped you and accept the ones who solved your issue.**2 -
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):
3
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 748 Beast Mode
- 60 App Studio
- 41 Variables
- 688 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 48 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive