Beast Mode to Categorize the data based upon the pattern

agolla2
agolla2 Member
in Beast Mode

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.

image.png


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

Tagged:

Best Answer

  • ggenovese
    ggenovese Contributor
    Answer ✓

    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

Answers

  • nmizzell
    nmizzell Contributor

    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.

    image.png

  • nmizzell
    nmizzell Contributor

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

  • agolla2
    agolla2 Member

    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
    ggenovese Contributor
    Answer ✓

    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

Categories