General
Highlighted
Fan

## Help on case statement

Hi
Good Day!

I have  a table with Two columns namely WO_Key , WO_DSC. I need to derive the flag based on the following logic

Input Table details:
--------------------------
WO_Key                    WO_DSC
--------------              --------------------
100                          PT
100                         NPT
100                         RC
100                         N/A
100                         SC
100                       UNKNWN
200                       NPT
200                       NPT
300                       PT
300                       RC
300                       NPT
400                     UNKNWN
400                     RC
400                     N/A

Expected Output :

WO_Key                    WO_DSC                   Flag
--------------              -------------------             --------------
100                          PT                                 SC
100                         NPT                                SC
100                         RC                                   SC
100                         N/A                                   SC
100                         SC                                   SC
100                       UNKNWN                         SC
200                       NPT                                  NPT
200                       NPT                                  NPT
300                       PT                                     PT
300                       RC                                     PT
300                       NPT                                   PT
400                     UNKNWN                              ?
400                     RCM                                      ?
400                     N/A                                        ?

logic to derive the FLAG:
1)For the set of same WO_KEY, If WO_DSC has atleast one record with the value 'SC' then FLAG=SC
2) for the set of same WO_KEY ,If WO_DSC Has the value of NPT for all the reacord then FLAG=NPT
3)For the set of same WO_KEY , if WO_DSC has atlease one PT value and none other records has 'SC' then FLAG=PT
4) For all other scenarios,Wecan populate FLAG as '?'

Note: WO_DSC has the below possible values
PT
NPT
RC
N/A
SC
UNKNWN

Tags (2)

Accepted Solutions
Junior Contributor

## Re: Help on case statement

You need conditional aggregation:

```CASE
--  If WO_DSC has at least one record with the value 'SC' then FLAG=SC
WHEN Max(CASE WHEN WO_DSC = 'SC'  THEN 1 ELSE 0 end) Over (PARTITION BY WO_KEY) = 1 THEN 'SC'
-- If WO_DSC Has the value of NPT for all the records then FLAG=NPT
WHEN Max(CASE WHEN WO_DSC = 'NPT' THEN 0 ELSE 1 end) Over (PARTITION BY WO_KEY) = 0 THEN 'NPT'
--  if WO_DSC has at least one PT value and none other records has 'SC' then FLAG=PT (covered by 1st WHEN)
WHEN Max(CASE WHEN WO_DSC = 'PT'  THEN 1 ELSE 0 end) Over (PARTITION BY WO_KEY) = 1 THEN 'PT'
ELSE '?'
END```
1 ACCEPTED SOLUTION
2 REPLIES
Junior Contributor

## Re: Help on case statement

You need conditional aggregation:

```CASE
--  If WO_DSC has at least one record with the value 'SC' then FLAG=SC
WHEN Max(CASE WHEN WO_DSC = 'SC'  THEN 1 ELSE 0 end) Over (PARTITION BY WO_KEY) = 1 THEN 'SC'
-- If WO_DSC Has the value of NPT for all the records then FLAG=NPT
WHEN Max(CASE WHEN WO_DSC = 'NPT' THEN 0 ELSE 1 end) Over (PARTITION BY WO_KEY) = 0 THEN 'NPT'
--  if WO_DSC has at least one PT value and none other records has 'SC' then FLAG=PT (covered by 1st WHEN)
WHEN Max(CASE WHEN WO_DSC = 'PT'  THEN 1 ELSE 0 end) Over (PARTITION BY WO_KEY) = 1 THEN 'PT'
ELSE '?'
END```
Fan

## Re: Help on case statement

Many Thanks for your timely help.