Collapse and Merge Columns
Hi, I'm trying to join two datasets together with a single shared field. I can do this just fine, but I'd like to collapse one column from both datasets into the same column and leave the rest of the columns intact. Below is an example of the column I'm trying to combine. I don't want to do a straight append though because the name column will show a ton of blanks (unless there's something I can do about that). Thoughts?
Comments
-
MySQL dataflow:
- SELECT
a.`ID`
,a.`Name`
,a.`Description`
,a.`Value 1`
,a.`Value 2`
,b.`Value 3`
FROM `Dataset 1` a
LEFT JOIN
`Dataset 2` b
ON a.`Description`=b.`Other Description`
Let me know if you prefer an ETL transform
0 - SELECT
-
This would work for a regular join but `Description` and `Other Description` will never match. I basically want to append the `Other Description` onto the first dataset table but also bring over all of the other columns.
0 -
You mean like a union?
try:
- SELECT
a.`ID`
,a.`Name`
,a.`Description`
,a.`Value 1`
,a.`Value 2`
,null as `Value 3`
FROM `Dataset 1` a
UNION
SELECT
b.`ID`
,b.`Name`
,b.`Other Description` As `Description`
,null as `Value 1`
,null as `Value 2`
,b.`Value 3`
FROM `Dataset 2` b
0 - SELECT
-
I think I was just able to get this to work using both methods.
- SELECT
a.`ID`
,a.`Name`
,a.`Description`
,a.`Value 1`
,a.`Value 2`
,'' as `Value 3`
FROM `Dataset 1` a
UNION ALL
SELECT
b.`ID`
,a.`Name`
,b.`Other Description` As `Description`
,'' as `Value 1`
,'' as `Value 2`
,b.`Value 3`
FROM `Dataset 2` b
LEFT JOIN `Dataset 1` a ON b.ID=a.ID
WHERE b.`Other Description` IS NOT NULL
1 - SELECT
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 306 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3K Transform
- 112 SQL DataFlows
- 649 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 787 Beast Mode
- 78 App Studio
- 43 Variables
- 742 Automate
- 187 Apps
- 474 APIs & Domo Developer
- 67 Workflows
- 14 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 406 Distribute
- 117 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 139 Manage
- 136 Governance & Security
- 8 Domo Community Gallery
- 44 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 113 Community Announcements
- 4.8K Archive