Beast Mode to Categorize the data based upon the pattern

agolla2

Hello,

Can anyone help me in writing this beast mode. Where I'm aiming to categorize these based on the status reversals from Yes to No.

Case when current ready status is No and previous status is Yes then considered as reversal.

Example like below: I already have first 3 columns in my data.

Tried this beast mode but not working.

CASE

    WHEN SUM(CASE 

        WHEN `Ready Status` = 'No' 

             AND `Previous Ready Status` = 'Yes'

             AND `Previous Ready Status` IS NOT NULL

        THEN 1

        ELSE 0

    END) 

    OVER (PARTITION BY `Case Number` ORDER BY `Case Number`) = 1 THEN '1 Time'


    WHEN SUM(CASE 

        WHEN `Ready Status` = 'No' 

             AND `Previous Ready Status` = 'Yes'

             AND `Previous Ready Status` IS NOT NULL

        THEN 1

        ELSE 0

    END) 

    OVER (PARTITION BY `Case Number` ORDER BY `Case Number`) = 2 THEN '2 Times'


    WHEN SUM(CASE 

        WHEN `Ready Status` = 'No' 

             AND `Previous Ready Status` = 'Yes'

             AND `Previous Ready Status` IS NOT NULL

        THEN 1

        ELSE 0

    END) 

    OVER (PARTITION BY `Case Number` ORDER BY `Case Number`) >= 3 THEN '3+ Times'


    ELSE 'No Reversal'

END

nmizzell

Hey Agolla,

I think that a beastmode is not the best suited tool for this task.

Try the following in magic ETL:

1. Group by Case number, count case status that equal "Yes" and count case status that equal "No"

2. Then, take the minimum of both these. This will give you the total number of reversals, assuming there are an even number plus one of yes and nos.

nmizzell

You can also do this in a pivot table if magic etl is not an option.

agolla2

Ok, thanks for the response.

But I have added a new column (Reversal Status) beside each row whether it is a reversal or not (Yes/No).

Case Number	Reversal Status
123	Yes
123	Yes
341	No
341	Yes

Now can I count how many 1,2, 3+ times for each case?

I need a single value of sum of cases where only 1 reversal occurred and likewise for 2, and 3+ times

ggenovese

Hi - your original beast mode was missing the second aggregation, you needed SUM(SUM(…

CASE

    WHEN SUM(SUM(CASE 

        WHEN `Ready Status` = 'No' 

             AND `Previous Ready Status` = 'Yes'

             AND `Previous Ready Status` IS NOT NULL

        THEN 1

        ELSE 0

    END)) 

    OVER (PARTITION BY `Case Number` ORDER BY `Case Number`) = 1 THEN '1 Time'


    WHEN SUM(SUM(CASE 

        WHEN `Ready Status` = 'No' 

             AND `Previous Ready Status` = 'Yes'

             AND `Previous Ready Status` IS NOT NULL

        THEN 1

        ELSE 0

    END)) 

    OVER (PARTITION BY `Case Number` ORDER BY `Case Number`) = 2 THEN '2 Times'


    WHEN SUM(SUM(CASE 

        WHEN `Ready Status` = 'No' 

             AND `Previous Ready Status` = 'Yes'

             AND `Previous Ready Status` IS NOT NULL

        THEN 1

        ELSE 0

    END)) 

    OVER (PARTITION BY `Case Number` ORDER BY `Case Number`) >= 3 THEN '3+ Times'


    ELSE 'No Reversal'

END

but also, I feel that you can get the same result without the window function

CASE 

WHEN SUM(CASE WHEN `ready status` = 'No' AND `previous ready status` = 'Yes' THEN 1 END) = 1 THEN '1 Time'

WHEN SUM(CASE WHEN `ready status` = 'No' AND `previous ready status` = 'Yes' THEN 1 END) = 2 THEN '2 Times'

WHEN SUM(CASE WHEN `ready status` = 'No' AND `previous ready status` = 'Yes' THEN 1 END) > 2 THEN '3+ Times'

ELSE 'No Reversal'

END