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
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 310 Workbench
- 7 Cloud Amplifier
- 9 Federated
- 3K Transform
- 113 SQL DataFlows
- 654 Datasets
- 2.2K Magic ETL
- 4K Visualize
- 2.5K Charting
- 796 Beast Mode
- 78 App Studio
- 44 Variables
- 757 Automate
- 188 Apps
- 480 APIs & Domo Developer
- 72 Workflows
- 17 DomoAI
- 40 Predict
- 17 Jupyter Workspaces
- 23 R & Python Tiles
- 408 Distribute
- 119 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 141 Manage
- 137 Governance & Security
- 8 Domo Community Gallery
- 47 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive