Grouping and comparing row values to create a flag

[Anonymous]

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

[qliksus]

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

[Anonymous]

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

[Anonymous]

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