Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arpitkharkia
Creator III
Creator III

Aggregate function

I have the following table:

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

Thanks!

1 Solution

Accepted Solutions
tresesco
MVP
MVP

Use FirstSortedValue() like in attached sample.

Capture.PNG

View solution in original post

9 Replies
sunny_talwar

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

Anil_Babu_Samineni

May be this?

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
avkeep01
Partner - Specialist
Partner - Specialist

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

rahulpawarb
Specialist III
Specialist III

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

arpitkharkia
Creator III
Creator III
Author

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!

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
arpitkharkia
Creator III
Creator III
Author

yeah right. may be this will help

Anil_Babu_Samineni

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

Or

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
tresesco
MVP
MVP

Use FirstSortedValue() like in attached sample.

Capture.PNG