topic Re: Firstsortedvalue Script Function in App Development

Firstsortedvalue Script Function

Anonymous — Thu, 26 Jul 2018 02:08:35 GMT

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

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

Thanks.

Will.

Re: Firstsortedvalue Script Function

KHSDM — Thu, 26 Jul 2018 02:31:34 GMT

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

order by

ID asc,

Date desc,

Order desc;

drop table Temp;

Re: Firstsortedvalue Script Function

Anonymous — Thu, 26 Jul 2018 06:40:33 GMT

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.

Re: Firstsortedvalue Script Function

sunny_talwar — Thu, 26 Jul 2018 12:07:41 GMT

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

];

Re: Firstsortedvalue Script Function

Anonymous — Thu, 26 Jul 2018 23:51:43 GMT

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.

Re: Firstsortedvalue Script Function

sunny_talwar — Fri, 27 Jul 2018 01:00:25 GMT

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