Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

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

ItemNoMyDateMyChar
101/05/2018N
101/06/2018Y
101/07/2018Y
101/08/2018Y
201/05/2018Y
201/06/2018N
201/07/2018N
301/07/2018N
301/08/2018Y

 

 

and this is what I want as output

ItemNoMyChar
1N
2Y
3N

 

  Please can somebody point me in the right direction?

 

 

 

 

1 Solution

Accepted Solutions
dplr-rn
Partner - Master III
Partner - Master III

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

View solution in original post

6 Replies
dplr-rn
Partner - Master III
Partner - Master III

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
Not applicable
Author

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
Not applicable
Author

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
Partner - Master III
Partner - Master III

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

nsetty
Partner - Creator II
Partner - Creator II

Try below expression

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

nsetty
Partner - Creator II
Partner - Creator II

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