Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

newqlik2017
Contributor

Pick the largest value from 4 columns

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:  

STATECITYPOLICYAMOUNT1AMOUNT2AMOUNT3AMOUNT4
NJEDISON100110000750750
NJJERSEY CITY200100700500
PAHARRISBURG30010500300600
PAPITTSBURG40014000800500
PAPHILADELPHIA5001900300500300
PAPHILADELPHIA5001900200500500

Expected Result:

 

STATECITYAMOUNT
NJEDISON750Amount 3 & Amount 4 are the lowest non-zero. Display Amount 3 when it's same as Amount 4
PAJERSEY CITY500Amount 4 is the lowest non-zero
PAHARRISBURG300Amount 3 is the lowest non-zero
PAPITTSBURG400Amount 1 is the lowest non-zero
PAPHILADELPHIA500Amount 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)

1 Solution

Accepted Solutions

Re: Pick the largest value from 4 columns

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

View solution in original post

2 Replies

Re: Pick the largest value from 4 columns

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

View solution in original post

Partner
Partner

Re: Pick the largest value from 4 columns

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