Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Talk to Experts, a LIVE Q&A Webinar. Bring your Qlik Sense Business questions on Aug. 4th. Register
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

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?

 

 

 

 

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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

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
Highlighted
Partner
Partner

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

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

Highlighted
Contributor II
Contributor II

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

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

Highlighted
Contributor II
Contributor II

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

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.

Highlighted
Partner
Partner

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

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

Highlighted
Partner
Partner

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

Try below expression

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

Highlighted
Partner
Partner

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

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