How can I remove items from a mega table using a beast mode?

charles · 2022-09-13T22:12:32+00:00

There was an error rendering this rich post.

I have a table with 5 columns: The first column (A) lists parts, the second (B) lists countries, the 3rd (C) is a concatenation of the first 2 columns, and the 4th (D) is a concatenation of the same list of parts but other related countries. There are many more countries in column D than in column C, so most of the values in column C are duplicated multiple times. The values in column D do not duplicate. Column E indicates if there is a match between columns C & D.

How can I write a beast mode that will remove all the rows in the mega table for the matching country from column B? (In the picture, yellow highlights the matching row and the red indicates the rows that need to be deleted)

Find more posts tagged with

Accepted answers

GrantSmith

You'd likely need to utilize a window function and a case statement. Something like this might work for you

MAX(MAX(CASE WHEN `Country1` = `Concat Field` THEN 1 ELSE 0 END)) OVER (PARTITION BY `Part1`, `Country1`)

Then use this field in the filter to ignore values of 1.

All comments

jaeW_at_Onyx

how do you know which row you want to exclude?

charles

Every row that is highlighted in red has Egypt in the second column and needs to be removed. The Egypt rows need to be removed because they match the Egypt in the last column. The last column is a based on a beastmode case statement that concatenates a part number with a country when columns 3 and 4 match. The last column may be optional. I had theorized that the last column would be used as a trigger to eventually contribute to a larger beastmode that would be needed to remove all the rows with a country that matches in columns 3 and 4. As an FYI, I only recently got permission/privileges to actually build datasets/dataflows and am inexperienced in doing so. I am fairly experienced in building beastmodes, and would prefer to make this work using a beastmode, if possible.

GrantSmith

You'd likely need to utilize a window function and a case statement. Something like this might work for you

MAX(MAX(CASE WHEN `Country1` = `Concat Field` THEN 1 ELSE 0 END)) OVER (PARTITION BY `Part1`, `Country1`)

Then use this field in the filter to ignore values of 1.

charles

Grant, your idea work!!! Thank you