Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Create a Straight Table
Dimension
Column1
Expression
Max(E_DATE) //For Max Date
FirstSortedValue(Column2,-(E_DATE+Column2/100000)) // For Column2
Create a Straight Table
Dimension
Column1
Expression
Max(E_DATE) //For Max Date
FirstSortedValue(Column2,-(E_DATE+Column2/100000)) // For Column2
Thanks Manish. It is working.
Can you explain the expression :FirstSortedValue(Column2,-(E_DATE+Column2/100000))
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.
CY
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 )