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
and this is what I want as output
Please can somebody point me in the right direction?
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.
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.
//FirstSortedValue([MyChar], Aggr(Min(distinct [MyDate]), [ItemNo]), 1)
(html, utf8, embedded labels, table is @1);
If(Peek('ItemNo') = ItemNo, RangeSum(Peek('Sequence'), 1), 1) as Sequence
order by ItemNo, MyDate;
Drop table TestMinDateNew;
drop table TestMinDate;
From Script side...