topic Re: Aggregation in QlikView

Aggregation

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

Hello,

I have following expression:

=aggr(

if(len(concat(total<Column1> E_DATE))=0,

aggr(if(%Column2 = MaxString(total<Column1>%Column2), %Column2),%Column2,Column1),

aggr(if(E_DATE = MAX(total<Column1>E_DATE), %Column2),%Column2,Column1)

)

,Column1

,%Column2)

Input table

Column1	Column2	E_DATE
18	794	2/2/2018 10:20:40 AM
18	873	2/2/2018 10:22:06 AM
18	110	2/2/2018 1:38:19 PM
18	795	2/2/2018 10:20:41 AM
18	874	2/2/2018 10:22:06 AM
18	114	2/2/2018 1:38:19 PM
20	445	2/2/2018 9:44:27 AM
20	407	2/2/2018 10:38:38 AM
20	552	2/2/2018 1:05:27 PM
20	41	2/2/2018 9:44:26 AM
20	404	2/2/2018 10:38:38 AM
20	548	2/2/2018 1:05:27 PM

Current output with above expression:

Column1	Column2	E_DATE
18	110	2/2/2018 1:38:19 PM
18	114	2/2/2018 1:38:19 PM
20	552	2/2/2018 1:05:27 PM
20	548	2/2/2018 1:05:27 PM

Required output:

Column1	Column2	E_DATE
18	114	2/2/2018 1:38:19 PM
20	552	2/2/2018 1:05:27 PM

Requirement: (Change above expression to get required output)

Check date against each group of column 1, display row with max date in group. And if within a group there are more rows that have same e_date than row that has max value in column 2 should be displayed.

Kindly help!

Re: Aggregation

MK_QSL — Tue, 20 Mar 2018 18:35:00 GMT

Create a Straight Table

Dimension

Column1

Expression

Max(E_DATE) //For Max Date

FirstSortedValue(Column2,-(E_DATE+Column2/100000)) // For Column2

Re: Aggregation

kinjal1645 — Wed, 21 Mar 2018 14:44:52 GMT

Thanks Manish. It is working.

Can you explain the expression :FirstSortedValue(Column2,-(E_DATE+Column2/100000))

Re: Aggregation

ychaitanya — Wed, 21 Mar 2018 15:11:02 GMT

First arguments specified - what column you need as an output

2 argument takes the expression on which we should derive the weight of object

by default it gives in ascending order, by using "-" before your 2nd arguments , we can make it act as Descending order.

3rd argument helps you to get the desired position in sort order( Top1 , Top2nd, Top 3rd..)

Hope this helps.

Below is standard explanation from QLIK

firstsortedvalue([{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression [, sort_weight [, n]])

returns the first value of expression sorted by corresponding sort-weight when expression is iterated over the chart dimension(s). Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return null. By stating an n larger than 1, you will get the nth value in order.

Examples:

firstsortedvalue ( PurchasedArticle, OrderDate )

firstsortedvalue ( PurchasedArticle, -OrderDate, 2 )

firstsortedvalue ( A/B, X*Y/3 )

firstsortedvalue ( distinct PurchasedArticle, OrderDate )

firstsortedvalue ( total PurchasedArticle, OrderDate )

firstsortedvalue ( total <Grp> PurchasedArticle, OrderDate )