How to find max value?

[#1]

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

[avinashelite]

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;

[Anonymous]

Hi Subbu,

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

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

[its_anandrjs]

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

[jagan]

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.

[MayilVahanan]

Hi

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;

[its_anandrjs]

Hi,

You need to group this data as well

Like

Result:

LOAD PartNo,

max(ReceiptDate) as ReceiptDate,

max(Unit Price) as Unit Price

resident

Temp

Group By PartNo;

Regards

Anand

[#8]

Hi,

Thanks to all..

I got the same in script level.

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

Sub2u444

[MayilVahanan]

Hi

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)

[its_anandrjs]

Hi,

Then simple in any straight table write

Dim:- PartNo,ReceiptDate

Exre:- MAX(UnitPrice)

Regards,

Anand