Beastmode filter to show when data exists in both datasets

Anna_Otake
Anna_Otake Contributor
in Beast Mode

Hi, I am trying to create a beast mode where if a campaign name matches in both Dataset A and Dataset B, then name the filter 'Match Campaign Name' and everything else 'Doesn't Match Campaign Name'.

Tagged:

Best Answers

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Ah gotcha. This is going to be best solved here in your ETL. Below is a link to a screen recording where I walk through one way you could solve this:

    https://www.loom.com/share/213f6231deb74ca59f6b006ab563c453?sid=2298a47c-9dc7-4eb4-bafb-e1977069daf7

    If I solved your problem, please select "yes" above

  • nmizzell
    nmizzell Contributor
    Answer ✓

    Hi Anna,

    I suggest you use a Full outer join on the campaign identifier within the ETL. Here is an example:

    image.png

    When you perform a full outer join and retain both identifier columns, you can read the nulls to determine which rows are present in either dataset, or both datasets. See the included beastmode for reference.

    Case

    when `Campaign ID (dataset A)` is null and `Campaign Id (dataset B)` is not null then 'Found in A, Missing in B'

    when `Campaign ID (dataset B)` is null and `Campaign Id (dataset A)` is not null then 'Found in B, Missing in A'

    else 'Found in Both Datasets'

    end

Answers

  • ColemenWilson
    ColemenWilson Coach
    edited October 2023

    Hi Anna, you can't create a beastmode to search between two datasets. You can combine those datasets into one and then have a beastmode search between two fields to see if there is a match. This would be the beastmode to check between columns:

    CASE WHEN `CampaignField1` = `CampaignField2` THEN 'Match Campaign Name' ELSE 'Does not Match Campaign Name' END

    If your data is not yet combined, you could create the flag within Magic ETL while you are joining the data. Does your campaign data have unique ID's you can join on instead of the campaign name?

    If I solved your problem, please select "yes" above

  • Anna_Otake
    Anna_Otake Contributor

    Hi @ColemenWilson

    Thank you for your help!
    The dataset is already appended (below is the screenshot) and essentially, I want to create a card that would be able to detect a campaign if they're named differently from both datasets.

    I'm not sure if my dataflow is incorrect or my beast mode is set up wrong.

    image.png

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    Ah gotcha. This is going to be best solved here in your ETL. Below is a link to a screen recording where I walk through one way you could solve this:

    https://www.loom.com/share/213f6231deb74ca59f6b006ab563c453?sid=2298a47c-9dc7-4eb4-bafb-e1977069daf7

    If I solved your problem, please select "yes" above

  • nmizzell
    nmizzell Contributor
    Answer ✓

    Hi Anna,

    I suggest you use a Full outer join on the campaign identifier within the ETL. Here is an example:

    image.png

    When you perform a full outer join and retain both identifier columns, you can read the nulls to determine which rows are present in either dataset, or both datasets. See the included beastmode for reference.

    Case

    when `Campaign ID (dataset A)` is null and `Campaign Id (dataset B)` is not null then 'Found in A, Missing in B'

    when `Campaign ID (dataset B)` is null and `Campaign Id (dataset A)` is not null then 'Found in B, Missing in A'

    else 'Found in Both Datasets'

    end

  • nmizzell
    nmizzell Contributor

    Correction: I reversed the order of the nulls in the beastmode. The corrected beastmode is as follows:

    Case

    when `Campaign ID (dataset A)` is null and `Campaign Id (dataset B)` is not null then 'Found in B, Missing in A'

    when `Campaign ID (dataset B)` is null and `Campaign Id (dataset A)` is not null then 'Found in A, Missing in B'

    else 'Found in Both Datasets'

    end

  • Anna_Otake
    Anna_Otake Contributor
    edited October 2023

    Thank you so much for the video @ColemenWilson ! I found your video very informative and easy to follow.
    This worked perfectly and I was able to create my card!

  • Anna_Otake
    Anna_Otake Contributor

    Thank you @nmizzell for your help as well! Very detailed and helpful!

Categories