Firstsortedvalue Script Function

[Anonymous]

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.

[KHSDM]

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;

[Anonymous]

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 · Accepted solution]

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

];

[Anonymous]

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