beast mode

Abe

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:

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.

Abe

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

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?

ggenovese

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

Abe

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

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.

Thank you again for the assistance.

Abe

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

ColemenWilson

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