topic Re: Check and count the matched cases in QlikView

Check and count the matched cases

priya945 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Check and count the matched cases

Taoufiq_Zarra — Tue, 11 Feb 2020 09:43:19 GMT

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;

Re: Check and count the matched cases

priya945 — Tue, 11 Feb 2020 09:50:27 GMT

😂Kudos and thank you Taoufiq. your logic works

Re: Check and count the matched cases

priya945 — Thu, 13 Feb 2020 07:37:05 GMT

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.

Re: Check and count the matched cases

Taoufiq_Zarra — Thu, 13 Feb 2020 08:39:24 GMT

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))

Re: Check and count the matched cases

priya945 — Thu, 13 Feb 2020 09:02:25 GMT

Thank You once again Taoufiq 😊