Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
];
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;
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.
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
];
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.
1E10 is another way of writing 10,000,000,000.... using this to give the sort weight of second to Order after Date...