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

charles
charles Member
in Charting

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)

image.png


Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    how do you know which row you want to exclude?

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • charles
    charles Member

    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
    GrantSmith Coach
    Answer ✓

    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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • charles
    charles Member

    Grant, your idea work!!! Thank you

Categories