Skip to content

    • Magic ETL

    Magic ETL

    Help create ETL

    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:

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • 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

    • Coach
      edited June 2024

      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

    • 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'

    • 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

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

    • 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

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

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

    • Member

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

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

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

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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories