# New to QlikView

Discussion board where members can get started with QlikView.

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:

 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)

1 Solution

Accepted Solutions MVP

## 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 MVP

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

## Re: Pick the largest value from 4 columns

You can use cross table for this.

Table1:

Table2:

State,

City,

Min(Amount) as Amount

Resident Table1

Group by State,City;

I hope this helps.

Regards,

Devyanshu Gupta