Help create ETL

Zel
Zel Member
in Magic 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

Tagged:

Best Answer

  • DavidChurchman
    DavidChurchman Coach
    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.

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited June 28

    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

  • Zel
    Zel Member

    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
    ColemenWilson Coach

    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

  • Zel
    Zel Member

    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)

    1. Create Filters for card 1
    2. Create filters for card 2
    3. Join data - card 1 and 2
    4. 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.
    image.png

    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
    ColemenWilson Coach

    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

  • DavidChurchman
    DavidChurchman Coach

    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.

  • Zel
    Zel Member

    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.

    image.png

    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)? 😅

    image.png

    After creating an ETL for this, I will just apply the filter 'Company Name not in MR' in the card level.

  • Zel
    Zel Member

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

  • MichelleH
    MichelleH Coach

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

  • Zel
    Zel Member

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

Categories