Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
martin_papier
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?

 

 

 

 

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

martin_papier
Contributor II
Contributor II
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

martin_papier
Contributor II
Contributor II
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...