topic Re: Aggregate function in QlikView

Aggregate function

arpitkharkia — Wed, 12 Apr 2017 10:54:03 GMT

I have the following table:

I want to extract the record with the lowest C_OutcomeOrder in a new table.

Thanks!

Re: Aggregate function

sunny_talwar — Wed, 12 Apr 2017 11:07:32 GMT

Extract where? Script or front end object? Would you be able to share a sample?

Re: Aggregate function

Anil_Babu_Samineni — Wed, 12 Apr 2017 11:45:18 GMT

May be this?

Sum({<C_OutcomeOrder = {'$(=Min(C_OutcomeOrder))'}>} C_OutcomeOrder)

Re: Aggregate function

avkeep01 — Wed, 12 Apr 2017 11:46:56 GMT

Hi Arpit,

Try the following expression:

ONLY({$<[Memo_Key]={"$(=SUBFIELD(CONCAT(IF(aggr(Rank(MIN(C_OutcomeOrder)),[Memo_Key])<=1,[Memo_Key]),'|',aggr(Rank(MIN(C_OutcomeOrder)),[Customer AR Name])) ,'|',1))"}>} [Memo_Key])

It sorts (usinge the range function) the Memo_key by C_OutcomeOrder and then picks the first row part within the concated fields using subfield

Kind regards,

Anton van Keep

Re: Aggregate function

rahulpawarb — Wed, 12 Apr 2017 11:49:28 GMT

Hello Arpit,

Trust that you are doing well!

Please refer given draft version of script:

Data:

LOAD Memo_Key,

Call_Number,

C_Outcome,

Date#(C_Date, 'DD/MM/YYYY') AS C_Date,

C_Time,

C_OutcomeOrder

FROM

AggregateFunctions.xlsx

(ooxml, embedded labels, table is Sheet1);

INNER JOIN

LatestRecord:

LOAD Memo_Key,

MinString(C_OutcomeOrder) AS C_OutcomeOrder

Resident Data

Group By Memo_Key;

Also refer the attached sample application.

Regards!

Rahul

Re: Aggregate function

arpitkharkia — Wed, 12 Apr 2017 12:01:49 GMT

Hi Anil,

This is working fine but only capturing data when the min value is 1. If for a MemoKey the min value is 3.04 its not showing. Help appreciated!

Re: Aggregate function

Anil_Babu_Samineni — Wed, 12 Apr 2017 12:04:14 GMT

Here, The condition we will give only Min. Value

May be you forget Arithmetic here. 1 and 3.04 - Min value is 1 itself. Don't forget basics

Re: Aggregate function

arpitkharkia — Wed, 12 Apr 2017 12:07:10 GMT

yeah right. may be this will help

Re: Aggregate function

Anil_Babu_Samineni — Wed, 12 Apr 2017 12:19:57 GMT

That is the case, You don't require Set Analysis at all. Just use sample Aggregation Min(C_OutcomeOrder)

Aggr(Min(C_OutcomeOrder), ChartDim1, ChartDim2,....)

Re: Aggregate function

tresB — Wed, 12 Apr 2017 12:19:59 GMT

Use FirstSortedValue() like in attached sample.