Qlik Sense - How to get the character value from the record with the minimum date in the set

[Anonymous]

I am trying to get the character value from the record with the minimum date in the set (the set being ItemNo).

Here is my source data

ItemNo	MyDate	MyChar
1	01/05/2018	N
1	01/06/2018	Y
1	01/07/2018	Y
1	01/08/2018	Y
2	01/05/2018	Y
2	01/06/2018	N
2	01/07/2018	N
3	01/07/2018	N
3	01/08/2018	Y

and this is what I want as output

ItemNo	MyChar
1	N
2	Y
3	N

  Please can somebody point me in the right direction?

[dplr-rn · Accepted solution]

Use below

FirstSortedValue(MyChar,MyDate)

Basically first value of MyChar when sorted by MyDate

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/BasicAggr...

[Anonymous]

Thank you very much Dilip, that worked.

I also need to get the character value from the maximum date, FirstSortedValue deal with that as well?

What I am trying to do is compare the first value against the last value and display the item if the values are different.

Kind Regards

Martin

[Anonymous]

I've sorted it.

This seems to give me what I want.

=aggr(if(FirstSortedValue(MyChar,MyDate) = 'N' and FirstSortedValue(MyChar,-1 * MyDate) = 'Y','Y'),ItemNo)

I can then filter out the nulls and have a list of items that have gone from N to Y.

[dplr-rn]

Did it work? if so close the thread by marking as answered

[nsetty]

Try below expression

FirstSortedValue([MyChar], Aggr(Min(distinct [MyDate]), [ItemNo]), 1)

[nsetty]

TestMinDate:
LOAD
    ItemNo,
    MyDate,
    MyChar
    //FirstSortedValue([MyChar], Aggr(Min(distinct [MyDate]), [ItemNo]), 1)
FROM [lib://1553137]
(html, utf8, embedded labels, table is @1);




TestMinDateNew:
Load *,
    If(Peek('ItemNo') = ItemNo, RangeSum(Peek('Sequence'), 1), 1) as Sequence
resident TestMinDate
order by ItemNo, MyDate;


TestMinDateFinal:
NoConcatenate
LOAD
    ItemNo,
    MyDate,
    MyChar
Resident TestMinDateNew
where Sequence=1;




Drop table TestMinDateNew;
drop table TestMinDate;

From Script side...