Check and count the matched cases

[priya945]

Hi All,

My data looks like

Case	Reason
1	Case A; Case B
2	Case B
3	Case C; Case A
4	Case D; Case B
5	Case A; Case C
6	Case B;Case D
7	Case E

Logic:   out of many cases in Reason field, i need to check if the case is present in a list(Case A,Case B,Case D)
& mark 1 against it if the case is present in Reason field and o for others.
Also if there are two cases are present in Reason filed then i need to mark 1 against the first case only

Finally count the 1's in frontend

Case	Reason	Case A	Case B	Case D
1	Case A; Case B	1	0	0
2	Case B	0	1	0
3	Case C; Case A	1	0	0
4	Case D; Case B	0	0	1
5	Case A; Case C	1	0	0
6	Case B;Case D	0	1	0
7	Case E	0	0	0
		3	2	1

Thanks in advance

[Taoufiq_Zarra · Accepted solution]

Data :

LOAD rowno() as IDcase,trim(subfield(Reason,';')) as Cases,* INLINE [
    Case,Reason
    1, Case A; Case B
    2, Case B
    3, Case C; Case A
    4, Case D; Case B
    5, Case A; Case C
    6, Case B;Case D
    7, Case E
];

FirstSortedValue:
noconcatenate
LOAD Case,FirstSortedValue(Cases, IDcase) as FirstCase Resident Data  where match(Cases,'Case A','Case B','Case D')>0 Group By Case ;
left join load * resident Data;

drop table Data;

output:
noconcatenate
load Case,Reason,if(FirstCase='Case A',1,0) as "Case A",if(FirstCase='Case B',1,0) as "Case B",if(FirstCase='Case D',1,0) as "Case D" 
resident FirstSortedValue;

drop table FirstSortedValue;

[priya945]

😂Kudos  and thank you Taoufiq. your logic works

[priya945]

Hi Taoufiq,

Could you please  do favor for below logic.  How to get sum of only listed cases(Case A, Case B, Case D)

Expected o/p is below 

	Sum([Case A]+Sum([Case B]+Sum([Case D]
Case A	3
Case B	2
Case D	1

From

Reason	Case A	Case B	Case D
Case A; Case B	1	0	0
Case A; Case C	1	0	0
Case B	0	1	0
Case B;Case D	0	1	0
Case C; Case A	1	0	0
Case D; Case B	0	0	1
	3	2	1

Thanks In advance.

[Taoufiq_Zarra · Accepted solution]

Hi Priya,

try to change Sum([Case A]+Sum([Case B]+Sum([Case D]

by :

=sum( aggr(only([Case D]),Reason))+sum( aggr(only([Case B]),Reason))+sum( aggr(only([Case A]),Reason))

[priya945]

Thank You once again Taoufiq 😊