Beastmode filter to show when data exists in both datasets

Anna_Otake

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

ColemenWilson

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

nmizzell

Hi Anna,

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

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

ColemenWilson

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?

Anna_Otake

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.

ColemenWilson

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

nmizzell

Hi Anna,

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

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

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

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

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