Aggregate Function Usage

[umashankarus]

Hi Experts

Below is my dataset in a straight table

Trying to fetch one row per document satisfying the following conditions

1) the document record with latest version (column # 2) [ ex: Doc # 1181 with ver 6 ]

AND

2) If version is same for a document, then fetch one with latest date (column #3) [ ex: Doc # 1188  with date 12/20/....]

In the expression, Used Aggregate function = MAX(Aggr(MAX(VERSION),DOC_NUMBER))

Result is correct only for 1 data row (ex: Doc # 1272 ]

What am I doing wrong here ?

Thanks for your help

Umashankar

[vinieme12]

Try

MAX(TOTAL <DOC_NUMBER> Aggr(MAX(VERSION),DOC_NUMBER))

[vinieme12]

please post excel sample

[umashankarus]

Hi,

I have attached the excel output after modification

This does not consider the 'Date' column

[vinieme12]

There seems to be something wrong with the timestamp here, but the remaining are correct

[umashankarus]

Could not open the file

Can you pl post the expressions

[vinieme12 · Accepted solution]

Try either of the below

if(NUM(CREATION_DATE) =  Max(TOTAL <DOC_NUMBER> CREATION_DATE)

,

Max(TOTAL <DOC_NUMBER> AGGR(MAX(VERSION),DOC_NUMBER))

)

OR

if(NUM(CREATION_DATE) =  Max(TOTAL <DOC_NUMBER> CREATION_DATE)

,

FirstSortedValue(TOTAL <DOC_NUMBER> VERSION,-CREATION_DATE)

)

//////////////LOAD SCRIPT////////////////////////

LOAD DOC_NUMBER,

     VERSION,

     timestamp#(trim(CREATION_DATE),'mm/dd/yyyy h:mm:ss TT') as CREATION_DATE,

     Aggr,

     [Aggr - What I want is like this]

FROM

(biff, embedded labels, table is Sheet1$);

//////////////////////////////////////////////////////////

[umashankarus]

Thanks Vineeth