topic Re: Pick the largest value from 4 columns in QlikView

Pick the largest value from 4 columns

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

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

EDISON

750

Amount 3 & Amount 4 are the lowest non-zero. Display Amount 3 when it's same as Amount 4

JERSEY CITY

500

Amount 4 is the lowest non-zero

HARRISBURG

300

Amount 3 is the lowest non-zero

PITTSBURG

400

Amount 1 is the lowest non-zero

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)

Re: Pick the largest value from 4 columns

sunny_talwar — Mon, 05 Mar 2018 18:35:44 GMT

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

Re: Pick the largest value from 4 columns

devyanshu_gupta — Tue, 06 Mar 2018 07:33:50 GMT

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