Removing Duplication and Ordering Objects within a Concatonated Field
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:
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."
Best Answers
-
@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.
2 -
@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:
- case
- when `Corporate Product Lines Owned` like '%Student Information System%' then 'Student Information System'
- else ''
- end
2
Answers
-
@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.
2 -
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.
0 -
@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:
- case
- when `Corporate Product Lines Owned` like '%Student Information System%' then 'Student Information System'
- else ''
- end
2 -
Thank you! This worked.
0
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 292 Workbench
- 4 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 95 SQL DataFlows
- 602 Datasets
- 2.1K Magic ETL
- 3.7K Visualize
- 2.4K Charting
- 695 Beast Mode
- 43 App Studio
- 39 Variables
- 658 Automate
- 170 Apps
- 441 APIs & Domo Developer
- 42 Workflows
- 5 DomoAI
- 32 Predict
- 12 Jupyter Workspaces
- 20 R & Python Tiles
- 386 Distribute
- 111 Domo Everywhere
- 269 Scheduled Reports
- 6 Software Integrations
- 113 Manage
- 110 Governance & Security
- 8 Domo University
- 30 Product Releases
- Community Forums
- 39 Getting Started
- 29 Community Member Introductions
- 98 Community Announcements
- Domo Community Gallery
- 4.8K Archive