Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
william_walsh2
Contributor II
Contributor II

Firstsortedvalue Script Function

Hi All,

Below is the data I would like to sort using the firstsortedvalue function.

My issues is if a milestone has the same date as another milestone it returns no value and it won't let me nest another firstsortedvalue in the sort-weight to use the order column to pick the milestone with the highest order.

How can I use the firstsortedvalue order to pick highest ordered milestone if the dates are the same.

This is my current load function.

Load

    ID,  

    FirstSortedValue(Milestone,-Date) as Milestone,

    max(Date) as Date

Resident Milestone

Group by ID

 

IDOrderMilestoneDate
11Milestone11/01/2017
12Milestone21/04/2017
13Milestone31/03/2017
14Milestone41/04/2017

Thanks.

Will.

1 Solution

Accepted Solutions
sunny_talwar

How about this

Table:

LOAD ID,

Max(Date) as Date,

FirstSortedValue(Milestone, -(Date + Order/1E10)) as Milestone

Group By ID;

LOAD * INLINE [

    ID, Order, Milestone, Date

    1, 1, Milestone1, 1/01/2017

    1, 2, Milestone2, 1/04/2017

    1, 3, Milestone3, 1/03/2017

    1, 4, Milestone4, 1/04/2017

];

View solution in original post

5 Replies
KHSDM
Creator II
Creator II

You can try use the FirstValue function and sort the data using order by.

Temp:

LOAD * INLINE [

    ID, Order, Milestone, Date

    1, 1, Milestone1, 1/01/2017

    1, 2, Milestone2, 1/04/2017

    1, 3, Milestone3, 1/03/2017

    1, 4, Milestone4, 1/04/2017

    2, 1, Milestone5, 1/01/2017

    2, 2, Milestone6, 1/04/2017

    2, 3, Milestone7, 1/03/2017

    2, 4, Milestone8, 1/04/2017

];

Temp2:

NoConcatenate

LOAD

    ID,

    FirstValue(Milestone) as Milestone,

    FirstValue(Date) as Date,

    max(Date) as DateAlt

Resident Temp

group by

ID

order by

ID asc,

Date desc,

Order desc;

drop table Temp;

william_walsh2
Contributor II
Contributor II
Author

Thanks for the help but it inst not working as in the dataset if there are no dates it still pulls in the firstvalue milestone so it shows a value if though there is no date for that value. I will work with this method and see if I can get it to work and let you know.

Also if I want to use the function below it returns null() even though there are dates. I know why it does but don't know how to make it look down the column until it finds a value if you null() the firstvalue because it is before it occur before the date in the if statement.

FirstValue(if(Date1 < '1/04/2017',Date1)) as Date1,

Let me know if there is another way to solve the problem.

Thanks.

Will.

sunny_talwar

How about this

Table:

LOAD ID,

Max(Date) as Date,

FirstSortedValue(Milestone, -(Date + Order/1E10)) as Milestone

Group By ID;

LOAD * INLINE [

    ID, Order, Milestone, Date

    1, 1, Milestone1, 1/01/2017

    1, 2, Milestone2, 1/04/2017

    1, 3, Milestone3, 1/03/2017

    1, 4, Milestone4, 1/04/2017

];

william_walsh2
Contributor II
Contributor II
Author

Hi Sunny,

Used your suggestion and everything is working as it should.

Just one question why have you added the "/1E10" after Order.

Thanks.

Will.

sunny_talwar

1E10 is another way of writing 10,000,000,000.... using this to give the sort weight of second to Order after Date...