topic Re: Grouping and comparing row values to create a flag in QlikView

Grouping and comparing row values to create a flag

Anonymous — Wed, 04 Oct 2017 03:42:44 GMT

I am trying to create a calculated column in load script or in report with the following logic. Here we have 2 sets of conditions because 1. 'selected' might come first or 2. 'discarded' might come first. I unable to build the logic to achieve such functionality. Need some expert advice. Thanks

For each individual , FLAG 'YES' when:

IF the ~<type> (ie. life, tpd) has a ~<status> = discarded,

IF there is the same ~<type> has a ~<status> = selected, AND

IF the discarded ~<score> is greater than selected ~<score>, AND

the ~<sum> is less than the selected ~<sum>

IF the ~<type> (ie. life, tpd) has a ~<status> = selected,

IF there is the same ~<type> has a ~<status> = discarded, AND

IF the selected ~<score> is less than discarded ~<score>, AND

the ~<sum> is greater than the discarded ~<sum>

ELSE 'NO'

ID	Customer	Type	Status	Score	Sum
1	a	life	selected	40	$4,000
1		ip
1		tpd
1		life	discarded	50	$3,000
2	b	life	discarded	60	$2,000
2		trauma
2		life	selected	80	$1,000
4	c	ip
4		life	discarded	20
4		life	selected	40

Re: Grouping and comparing row values to create a flag

qliksus — Wed, 04 Oct 2017 08:05:03 GMT

Can you explain what you wanted as the expected output ? you explanation is not quite clear

Re: Grouping and comparing row values to create a flag

Anonymous — Wed, 04 Oct 2017 10:58:58 GMT

I wanted to create a flag (YES/NO) in my report based on the above logic.

Re: Grouping and comparing row values to create a flag

Anonymous — Wed, 04 Oct 2017 22:43:53 GMT

Something like this :

ID	Customer	Type	Status	Score	Sum	Discard Warning
1	Alex	life	selected	40	$4,000	Yes
1		ip
1		tpd
1		life	discarded	50	$3,000
2	Stuart	life	discarded	60	$2,000	No
2		trauma
2		life	selected	80	$1,000
4	John	ip				Yes
4		life	discarded	40	$500
4		life	selected	20	$1,000