Aggregate function

[arpitkharkia]

I have the following table:

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

Thanks!

[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)

[avkeep01]

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]

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]

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

[arpitkharkia]

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,....)

[tresesco · Accepted solution]

Use FirstSortedValue() like in attached sample.