Help create ETL

Zel · 2024-06-28T13:47:21+00:00

There was an error rendering this rich post.

Zel

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

Find more posts tagged with

Magic ETL

Accepted answers

DavidChurchman

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.

All comments

ColemenWilson

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

Zel

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'

ColemenWilson

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.

Zel

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.

ColemenWilson

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)

DavidChurchman

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.

Zel

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.

Zel

following up on this thread please, thank you. hope someone can help me.

MichelleH

@Zel Can you please explain what you are trying to accomplish in terms of business logic and what you want your final dataset to look like? That will help us point you in the right direction.

DavidChurchman

Zel

@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.