Issue with Nested Case Statements

MosesWynn
MosesWynn Member
in Magic ETL

I have a dataset of call records. I am attempting to filter out records that fall outside of business hours when the 'Call Result' is 'No Agents in Queue,' based on the product the call was associated to. I have created the following Beast Mode calculation to do so:

 

 

CASE `Last Leg Product`
WHEN 'NPAC-CCS' THEN
  (CASE WHEN (HOUR(TIME(`Call Date`)) < 9 OR HOUR(TIME(`Call Date`)) >= 18) AND `Call Result` = 'No Agents In Queue' THEN 1 ELSE 0 END)
ELSE
  (CASE WHEN (HOUR(TIME(`Call Date`)) < 8 OR HOUR(TIME(`Call Date`)) >= 20) AND `Call Result` = 'No Agents In Queue' THEN 1 ELSE 0 END)
END

 

 

 There is a filter on the card called to show only cases where 'No Agents Business Hours Filter' = 0. All other products are correct, but for 'NPAC-CCS' calls there are still cases outside of the defined business hours.

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @MosesWynn 

    Are all of your NPAC-CCS records still appearing or are some records being filtered / identified correctly (Essentially - are you getting into the first section of your beast mode)? Is it possible there's whitespace around the `Last Leg Product` field (did you try and wrap it in a TRIM function)?

     

    Have you tried rewriting your CASE to be CASE WHEN `Last Leg Product` = 'NPAC-CCS'

    (they should be logically identical though)?

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

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @MosesWynn 

    Are all of your NPAC-CCS records still appearing or are some records being filtered / identified correctly (Essentially - are you getting into the first section of your beast mode)? Is it possible there's whitespace around the `Last Leg Product` field (did you try and wrap it in a TRIM function)?

     

    Have you tried rewriting your CASE to be CASE WHEN `Last Leg Product` = 'NPAC-CCS'

    (they should be logically identical though)?

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

    Hi @GrantSmith,

     

    Thanks for your response.

     

    'NPAC-CCS' records that fall outside of the span 8AM to 8PM are being filtered out, as per the ELSE clause. I reformatted to the following, but am still getting the same results.

    CASE 
WHEN TRIM(`Last Leg Product`) = 'NPAC-CCS ' THEN
  (CASE WHEN (HOUR(TIME(`Call Date`)) < 9 OR HOUR(TIME(`Call Date`)) >= 18) AND `Call Result` = 'No Agents In Queue' THEN 1 ELSE 0 END)
ELSE
  (CASE WHEN (HOUR(TIME(`Call Date`)) < 8 OR HOUR(TIME(`Call Date`)) >= 20) AND `Call Result` = 'No Agents In Queue' THEN 1 ELSE 0 END)
END

     

    Thanks!

    Moses

  • GrantSmith
    GrantSmith Coach

    Hi @MosesWynn 

     

    Did you mean to have the trailing space in 'NPAC-CCS '? Doing a TRIM on your column and comparing it to the value with a trailing space will never match up.

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

    Actually, your suggestion worked. I'm realizing I accidentally had a space after CCS in my formula.

     

    Thanks!

Categories