topic Re: How to find max value? in QlikView

How to find max value?

Mon, 14 Mar 2016 05:28:47 GMT

Hi Folks,

How to find the max value based on receipt date...

I want to show only max receiptdate of max unit price

Regards,

Sub2u444

Re: How to find max value?

avinashelite — Mon, 14 Mar 2016 05:35:11 GMT

something like this :

Temp:

LOAD *

from table ;

noconcatenation

Result:

LOAD PartNo,

max(ReceiptDate) as ReceiptDate,

max(Unit Price) as Unit Price

resident

Temp

Group by PartNo;

Drop table Temp;

Re: How to find max value?

Anonymous — Mon, 14 Mar 2016 05:35:35 GMT

Hi Subbu,

Try to use the Aggr function and giving a sample Expression as Follows....

Aggr(if(Rank(Sum([Unit Price])<=1,ReceiptDate),ReceiptDate)

Re: How to find max value?

its_anandrjs — Mon, 14 Mar 2016 05:38:27 GMT

Hi,

In the load script you can create new table to find out the Max UnitPrice at date level.

Ex:-

Data:

LOAD * Inline

[

PartNo,ReceiptDate,UnitPrice

1,13/01/2016,30

1,20/02/2016,10

1,15/03/2016,20

1,15/03/2016,40

];

Maxtab:

LOAD

ReceiptDate,

MAX(UnitPrice) as Max_UnitP

Resident Data

Group By ReceiptDate;

Regards,

Anand

Re: How to find max value?

jagan — Mon, 14 Mar 2016 05:38:44 GMT

HI,

Try like this

Data:

LOAD

FROM DataSource;

LEFT JOIN(Data)

LOAD

PartNo,

max(Unit Price) as Unit Price,

1 AS MaxPriceFlag

RESIDENT Data

GROUP BY PartNo;

Now in front end use below expression for getting max date of the Part No.

Max({<MaxPriceFlag={1}>}ReceiptDate)

Hope this helps you.

Regards,

Jagan.

Re: How to find max value?

MayilVahanan — Mon, 14 Mar 2016 05:40:04 GMT

Try like this

Test:

LOAD *, RowNo() as RowNo Inline

[

PartNo, ReceiptDate, UnitPrice

1, 13/01/2016,30

1, 20/02/2016, 10

1, 15/03/2016, 20

1, 15/03/2016, 40

];

LOAD PartNo, FirstSortedValue(ReceiptDate, -ReceiptDate&RowNo), FirstSortedValue(UnitPrice, -ReceiptDate&RowNo) As UnitPrices Resident Test

Group by PartNo;

DROP Table Test;

Re: How to find max value?

its_anandrjs — Mon, 14 Mar 2016 05:40:20 GMT

Hi,

You need to group this data as well

Result:

LOAD PartNo,

max(ReceiptDate) as ReceiptDate,

max(Unit Price) as Unit Price

resident

Temp

Group By PartNo;

Regards

Anand

Re: How to find max value?

Mon, 14 Mar 2016 05:40:21 GMT

Hi,

Thanks to all..

I got the same in script level.

But i am trying to do the same in expression level...

Sub2u444

Re: How to find max value?

MayilVahanan — Mon, 14 Mar 2016 05:44:03 GMT

Try like this

Test:

LOAD *, RowNo() as RowNo Inline

[

PartNo, ReceiptDate, UnitPrice

1, 13/01/2016,30

1, 20/02/2016, 10

1, 15/03/2016, 20

1, 15/03/2016, 40

];

Chart:

Dim: PartNo

Exp:

ReceiptDate = FirstSortedValue(ReceiptDate, - ReceiptDate&RowNo)

UnitPrice = FirstSortedValue(UnitPrice, - ReceiptDate&RowNo)

Re: How to find max value?

its_anandrjs — Mon, 14 Mar 2016 05:44:14 GMT

Hi,

Then simple in any straight table write

Dim:- PartNo,ReceiptDate

Exre:- MAX(UnitPrice)

Regards,

Anand

Re: How to find max value?

Kushal_Chawda — Mon, 14 Mar 2016 05:47:24 GMT

In front end You can do this by expression

=FirstSortedValue(distinct ReceiptDate, - aggr(max(UnitPrice),PartNo))

In script,

Data:

Load PartNo,

ReceiptDate,

UnitPrice

From Table;

left join

load max(UnitPrice) as MaxUnitPrice,

PartNo,

1 as MaxUnitPriceFlag

Resident Data

group by PartNo;

left join

load PartNo,

max(ReceiptDate) as MaxReceiptDate

resident Date

where MaxUnitPriceFlag=1

group by PartNo;

Re: How to find max value?

Mon, 14 Mar 2016 05:48:35 GMT

Hi,

I got like this..

I want the output like this..

PartNo	ReceiptDate	Unit Price
1	15/03/2016	40
2	15/05/2016	80
10	01/08/2015	90

Sub2u444

Re: How to find max value?

avinashelite — Mon, 14 Mar 2016 05:48:42 GMT

Thanks a lot its.anandrjs‌ its was a typo error from me

Re: How to find max value?

Anonymous — Mon, 14 Mar 2016 05:49:59 GMT

Hi Subbu,

Try to use the Aggr function and giving a sample Expression as Follows....

Aggr(if(Rank(Sum([Unit Price])<=1,ReceiptDate),ReceiptDate)

Re: How to find max value?

Mon, 14 Mar 2016 05:54:55 GMT

Hi,

Sorry, not getting the expected output..

getting like this..

Sub2u444

Re: How to find max value?

Kushal_Chawda — Mon, 14 Mar 2016 05:58:26 GMT

try below expression

=FirstSortedValue(distinct UnitPrice, - aggr(max(ReceiptDate),PartNo))

FirstSortedValue(distinct UnitPrice, - ReceiptDate)

Re: How to find max value?

avinashelite — Mon, 14 Mar 2016 06:01:01 GMT

Try like this :

Max({<ReceiptDate={"=aggr(max(ReceiptDate),PartNo) ">}UnitPrice)

Re: How to find max value?

Mon, 14 Mar 2016 06:06:17 GMT

Hi,

not getting any data..

Sub2u444.

Re: How to find max value?

its_anandrjs — Mon, 14 Mar 2016 06:09:42 GMT

Better if you create the Flag into load script.

Regards

Anand

Re: How to find max value?

avinashelite — Mon, 14 Mar 2016 06:10:34 GMT

Try this

=aggr(max(UnitPrice),PartNo)