Help create ETL
Hello. I have 3 cards in my dashboard. Can anyone help me create an ETL for the 3rd column only, please?
Here are the details and filters for each card:
Card 1:
Company Name
not in 'MR'
Destination
contains 'MR', 'TC', 'L', 'NP', 'PP'
show count of ID
Card 2:
Company Name
in `MR'
show count of ID
Card 3 (here's where I'm having a hard time, please help)
Combined output of Cards 1 and 2
Remove duplicate ID
Apply Filter: Company Name
does not contain 'MR'
show count of ID
Best Answer
-
It's a little unclear on what you're trying to do, but since your step 1 and 2 are mutually exclusive based on their filters, I think you probably don't want a join, since there won't be any matches to join on. You probably want to combine the data using an "Append" tile instead, which stacks the data on top of itself.
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0
Answers
-
Do you mean a beastmode? Or a new column using a formula tile? Either way, here is the code for it:
COUNT(DISTINCT CASE WHEN (`Company Name` NOT LIKE ('MR') AND `Destination` IN( 'MR', 'TC', 'L', 'NP', 'PP')) OR `Company Name` = 'MR' THEN `ID` END)You can't have logic that says
If I solved your problem, please select "yes" above
0 -
Hi @ColemenWilson, I think it's a bit more complicated than that. The thing is, I need to combine the output for cards 1 and 2 first. Then I have to remove duplicate IDs from there
and apply the filter:
Company Name
does not contain 'MR'0 -
Can you just type out the logic you need? I am confused when you say combine logic from 1 and 2 because there is some conflicting logic like Company name = MR and at the same time not = MR.
If I solved your problem, please select "yes" above
0 -
I think ETL is a better option for this, it's just that I think I'm missing something or I'm using wrong "Join Data" option not sure what to choose (inner, left, right, full)
- Create Filters for card 1
- Create filters for card 2
- Join data - card 1 and 2
- trim / remove excess characters from -XXXXX (how can I do this, too?) I only need the digits before the hyphen (-) . Anything from the hyphen (-) and beyond that should be removed.
5. remove duplicates for ID
After creating an ETL for this, I will just apply the filter
Company Name
not in MR in the card itself.0 -
ETL is just a series of tables you are creating. With each tile you are creating a new data table. You can apply the logic you need in a formula or filter tile and then from there create another formula/filter tile and so on.
For the join data step in #3, you'll need to provide more information about the problem you are having.
For #4 you can use SUBSTRING_INDEX() to remove the excess characters:
SUBSTRING_INDEX(`Global Device ID`,'-',1)
If I solved your problem, please select "yes" above
0 -
For #4, I would recommend the 'Split Column' tile, because it lets you keep the "discarded" characters in a separate column, in case you need to reference them later. (You can always drop the extra column using a the "Alter Columns" tile.
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0 -
Hello @ColemenWilson and @DavidChurchman. Thank you all for looking into this.
For context, I have separate dashboard cards using the original dataset (Tracker_Snowflake_Dataset) for Cards 1 and 2 showing the filters below.
Then I have to come up with Card 3, combining data for both Cards 1 and 2, following the steps I provided in the previous comment. Since Cards 1 and 2 have conflicting logics (based on each of their filters), I'm trying to come up with an ETL to combine them and apply the steps.
Here's an overview of the ETL I tried to create. I think I'm having a problem with Step #3. I just need to combine ALL data from Step #1 and Step #2. Not sure what to use (inner, left, right, full), then which table should be altered / fixed (left or right)? 😅
After creating an ETL for this, I will just apply the filter '
Company Name
not in MR' in the card level.0 -
following up on this thread please, thank you. hope someone can help me.
0 -
It's a little unclear on what you're trying to do, but since your step 1 and 2 are mutually exclusive based on their filters, I think you probably don't want a join, since there won't be any matches to join on. You probably want to combine the data using an "Append" tile instead, which stacks the data on top of itself.
Please 💡/💖/👍/😊 this post if you read it and found it helpful.
Please accept the answer if it solved your problem.
0 -
@DavidChurchman I think that make sense. 😀 I will try your suggestion with my ETL now. Thank you for your idea! I will let you know if it works.
0
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 677 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 45 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive