Removing Duplication and Ordering Objects within a Concatonated Field

n____l____
n____l____ Member
in Magic ETL

Hello,

Our company offers several different product lines and I am attempting to count how many times each combination is owned. There is a field that comes through to Domo from a SFDC connector with this information, but there is a ton of duplication within as a customer can own multiple of 1 product. Example:

Capture.PNG

I'm struggling to figure out how I can remove any duplicated names and then alphebatize them within this field so that, when grouped, I'm not getting seperate buckets for "Student Information System, ERP" and "ERP, Student Information System."

Tagged:

Best Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @n____l____ Do you have a list of all possible products that could appear in the list? If so, you could use a formula tile in ETL to create a column to check whether each product is listed. For example, the column to check whether Student Information System is in the list would look like this: 

    case when `Corporate Product Lines Owned` like '%Student Information System%' then 'Student Information System' end

    Then you can re-concatenate all the new columns in the desired order without duplication.

  • MichelleH
    MichelleH Coach
    Answer ✓

    @n____l____ Removing the "end" after your concat function should resolve the syntax error. If the nulls are still an issue, then you may need to update your 19 columns to show an empty string instead of null like this:

    1. case
    2. when `Corporate Product Lines Owned` like '%Student Information System%' then 'Student Information System'
    3. else ''
    4. end


Answers

  • MichelleH
    MichelleH Coach
    Answer ✓

    @n____l____ Do you have a list of all possible products that could appear in the list? If so, you could use a formula tile in ETL to create a column to check whether each product is listed. For example, the column to check whether Student Information System is in the list would look like this: 

    case when `Corporate Product Lines Owned` like '%Student Information System%' then 'Student Information System' end

    Then you can re-concatenate all the new columns in the desired order without duplication.

  • n____l____
    n____l____ Member

    Thanks Michelle! I do have a list of all possible values - 19 to be exact. Having trouble re-concatonating after the extrapolation (I think) because of the null values? I Just keep recieving a syntax error.


    Using:

    CONCAT(`CPL 1`,`CPL 2`,`CPL 3`,`CPL 4`,`CPL 5`,`CPL 6`,`CPL 7`,`CPL 8`,`CPL 9,``CPL 10`,`CPL 11`,`CPL 12`,`CPL 13`,`CPL 14`,`CPL 15`,`CPL 16`,`CPL 17`,`CPL 18`,`CPL 19`) end


    with each CPL being a column for an individual product line.

  • MichelleH
    MichelleH Coach
    Answer ✓

    @n____l____ Removing the "end" after your concat function should resolve the syntax error. If the nulls are still an issue, then you may need to update your 19 columns to show an empty string instead of null like this:

    1. case
    2. when `Corporate Product Lines Owned` like '%Student Information System%' then 'Student Information System'
    3. else ''
    4. end


  • n____l____
    n____l____ Member

    Thank you! This worked.

Categories