Pick the largest value from 4 columns

[newqlik2017]

Hello,

How can I get the lowest value among 4 amount columns for a given policy. Attached is the qvw for reference.

For instance,

Given Data Set:  

STATE	CITY	POLICY	AMOUNT1	AMOUNT2	AMOUNT3	AMOUNT4
NJ	EDISON	1001	1000	0	750	750
NJ	JERSEY CITY	2001	0	0	700	500
PA	HARRISBURG	3001	0	500	300	600
PA	PITTSBURG	4001	400	0	800	500
PA	PHILADELPHIA	5001	900	300	500	300
PA	PHILADELPHIA	5001	900	200	500	500

Expected Result:

STATE	CITY	AMOUNT
NJ	EDISON	750	Amount 3 & Amount 4 are the lowest non-zero. Display Amount 3 when it's same as Amount 4
PA	JERSEY CITY	500	Amount 4 is the lowest non-zero
PA	HARRISBURG	300	Amount 3 is the lowest non-zero
PA	PITTSBURG	400	Amount 1 is the lowest non-zero
PA	PHILADELPHIA	500	Amount 2 is the lowest non-zero (sum of 300+200; Amount 1 is the 900 because that's applies only once to a given policy)

[sunny_talwar · Accepted solution]

May be this

RangeMin(

If(Sum(AMOUNT1) = 0, 1E10, Sum(AMOUNT1)),

If(Sum(AMOUNT2) = 0, 1E10, Sum(AMOUNT2)),

If(Sum(AMOUNT3) = 0, 1E10, Sum(AMOUNT3)),

If(Sum(AMOUNT4) = 0, 1E10, Sum(AMOUNT4)))

[devyanshu_gupta]

You can use cross table for this.

Table1:

Crosstable(Amount_no,Amount,3) Load * From Table;

Table2:

Load

State,

City,

Min(Amount) as Amount

Resident Table1

Group by State,City;

I hope this helps.

Regards,

Devyanshu Gupta