Can my output include projects that don't have a column value for a Split / Group

PJG
PJG Member
in Magic ETL

Hi all,

I have this ETL which takes a multi selection attribute (Div) with 19 values and does the following:

  1. Splits each selection option into its own column
  2. Converts the columns to rows
  3. Filters to remove instances where there was no delimiter, because if only 1 of the 19 values was selected - these showed up as "Not Defined" on a second row
  4. Groups the 19 selection options into just 5
  5. Adds the formula CONCAT(`ProjectName`,`Div Grouping`) to identify unique combinations of Name and Div
  6. Adds in other relevant columns
  7. Removes the duplicates based on the CONCAT
image.png

It is working as intended where Div is set. However, is it possible to somehow still include projects that don't have this attribute set? I mentioned above in step 3, that it somewhat erroneously showed a Not Defined when it could not find the delimiter because only 1 value was selected, but I'd really want it to show Not Defined when no selection options are made.

Any solution to this? Hand holding greatly appreciated; this is only my 2nd day of using ETL, so still lots to learn! 😀

Best Answer

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    If that is the case, you could add a path to your ETL that filters to only rows with null values for Div and then append those to the rest of your data.

    If I solved your problem, please select "yes" above

Answers

  • nmizzell
    nmizzell Contributor

    Hey PJG,

    Could you provide an example input and desired output?

    Thanks,

  • PJG
    PJG Member

    Hi nmizzel - thanks for the quick reply.

    Here's a very simple example of the input and the desired output

    image.png

    The issue is that I cannot see Project4 in the Output, but I suspect it's being removed at the Split action, since it has no Div values set.

    Thanks

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    If that is the case, you could add a path to your ETL that filters to only rows with null values for Div and then append those to the rest of your data.

    If I solved your problem, please select "yes" above

  • PJG
    PJG Member

    Thank you for pointing me in the right direction, Colemen. Got it working with this:

    image.png

Categories