beast mode

Abe
Abe Member
in Beast Mode

hi everyone,

Im in need of your assistance on completing a beast mode please.

I have data that lists out site names on each row with service types but I only want sites that list the 3 types - EPIK or Greenix and Survey and Cutover and Test

Like the image below:

image.png

and this is what I have so far but not giving me the correct data
Case
WHEN Site_Name = ServiceType AND ServiceType = 'Survey' THEN ' Cutover and Test'
WHEN Site_Name= 'Greenix' AND ServiceType = 'EPIX' THEN 0 Else 1 END

Any assistance is greatly appreciated.

Tagged:

Best Answers

  • ggenovese
    ggenovese Contributor
    Answer ✓

    If I am understanding correctly, you should be able to do the following steps using Magic ETL:

    1. add a filter for Types IN ('Survey', 'Cutover and Test', 'EPIK','Greenix' )
    2. add a Group By and group by Site and count distinct types
    3. add a filter and filter on count = 3
    4. join that back to the main dataset on site
    image.png

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    I understand now, thank you! Yes you will want to do what @ggenovese recommended.

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

Answers

  • Abe
    Abe Member

    To be clear, we want the data to show on each row - 3 of the same sites with a Survey Type row , Cutover and Test type row and EPIK or Greenix type row.

  • ColemenWilson
    ColemenWilson Coach

    I've re-read this like 10 times and can't figure out exactly what you are trying to do. Could you share once again slightly differently? Or maybe show two excel samples: what the data currently looks and how you would like the data to look with the beastmode?

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

  • ggenovese
    ggenovese Contributor
    Answer ✓

    If I am understanding correctly, you should be able to do the following steps using Magic ETL:

    1. add a filter for Types IN ('Survey', 'Cutover and Test', 'EPIK','Greenix' )
    2. add a Group By and group by Site and count distinct types
    3. add a filter and filter on count = 3
    4. join that back to the main dataset on site
    image.png

  • Abe
    Abe Member

    I do apologize if my explanation was not clear or understandable. @ColemenWilson

    Here is the excel sheet with the just basic filters of only receiving data showing sites with specific Types.

    The 3rd Type can be either or but a site must have a "Survey" ticket then a "Cutover and Test" ticket and then either an "EPIK" or "Greenix" ticket. And the below screenshot shows all sites with any Types connected to them

    1: Survey

    2:Cutover and Test

    3: EPIK or Greenix

    image.png

    Now, what I want to see are only sites that has all 3 Types of tickets and remove sites that do not.

    So if a site only has a Survey and a Cutover and Test ticket but not an EPIK/Greenix then we do not want that data to be shown.

    Please see below. This is an example what I want the data to show. Any site that has all 3 ticket types.

    image.png

    Thank you again for the assistance.

  • Abe
    Abe Member

    @ggenovese Thank you, let me give that a try as well.

  • ColemenWilson
    ColemenWilson Coach
    Answer ✓

    I understand now, thank you! Yes you will want to do what @ggenovese recommended.

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

  • Abe
    Abe Member

    @ColemenWilson @ggenovese

    Thank you for the assistance! That definitely helped me out greatly.

    Thank you and Happy Holidays to you and your families!

Categories