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

PJG

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

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! 😀

ColemenWilson

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.

nmizzell

Hey PJG,

Could you provide an example input and desired output?

Thanks,

PJG

Hi nmizzel - thanks for the quick reply.

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

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

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.

PJG

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