topic Re: Aggregate Function Usage in QlikView

Aggregate Function Usage

umashankarus — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggregate Function Usage

vinieme12 — Sun, 29 Jan 2017 08:29:04 GMT

Try

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

Re: Aggregate Function Usage

vinieme12 — Sun, 29 Jan 2017 08:31:56 GMT

please post excel sample

Re: Aggregate Function Usage

umashankarus — Sun, 29 Jan 2017 08:41:11 GMT

Hi,

I have attached the excel output after modification

This does not consider the 'Date' column

Re: Aggregate Function Usage

vinieme12 — Sun, 29 Jan 2017 10:01:20 GMT

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

Re: Aggregate Function Usage

umashankarus — Sun, 29 Jan 2017 17:45:47 GMT

Could not open the file

Can you pl post the expressions

Re: Aggregate Function Usage

vinieme12 — Sun, 29 Jan 2017 20:13:21 GMT

Try either of the below

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

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

)

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

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

Re: Aggregate Function Usage

umashankarus — Sun, 29 Jan 2017 20:27:06 GMT

Thanks Vineeth