Collapse and Merge Columns

jlazerus
jlazerus Member
in Magic ETL

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?

 

2019-07-03 17_04_50-Book1 - Excel.png

Comments

  • ST_Superman
    ST_Superman Domo Employee

    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

  • jlazerus
    jlazerus Member

    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.

  • ST_Superman
    ST_Superman Domo Employee

    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
  • jlazerus
    jlazerus Member

    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

     

Categories