Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
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)))

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

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