Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear Experties,
How to find out latest record with latest status.please see the below example table :
ID | StartDate | Days | StatusReason | ||
A1001 | 01/01/2017 10:08:11 | 10 | closed | ||
A1001 | 01/01/2017 11:08:11 | 15 | Pending | latest record | |
A1001 | 31/01/2017 10:08:11 | 30 | closed | ||
A1002 | 01/02/2017 12:10:13 | 12 | Pending | ||
A1002 | 21/02/2017 12:10:13 | 15 | closed | Latest Record |
i would like to expecting like below
ID | StartDate | Days | StatusReason |
A1001 | 01/01/2017 11:08:11 | 15 | Pending |
A1002 | 21/02/2017 12:10:13 | 15 | - |
It would be helpfull if anyone can provide the logic.
Regards,
Sunny
ID | Maxstring(StartDate) | FirstSortedValue(Days, -StartDate) | FirstSortedValue(StatusReason, -StartDate) |
---|---|---|---|
A1001 | 31.01.2017 10:08:11 | 30 | closed |
A1002 | 21.02.2017 12:10:13 | 15 | closed |
For A1001, shouldn't the record with 31/12/2017 be picked?
In general, you can create a table chart with dimension ID and use FirstSortedValue in your expression:
=FirstSortedValue( StatusReason, -StartDate)
This requires that StartDate shows a numeric representation, i.e. the timestamp should be correctly interpreted as such.
Set the TimestampFormat to your format to support a correct interpretation in your script.
Hi Stefan ,
Thank you for the reply,it was typo .it is 31/01/2017 10:08:11.
Hi,
I need to know all the column information same like below not only for ID and status reason
ID | StartDate | Days | StatusReason |
A1001 | 01/01/2017 11:08:11 | 15 | Pending |
A1002 | 21/02/2017 12:10:13 | 15 | - |
You can find the StartDate with
=MaxString(StartDate)
in a chart with dimension ID, and respective Days and StatusReason can be retrieved using FirstSortedValue() as indicated above.
Hi ,
I am not able to get exactly output result .the below results i am getting .
Seems like you are using additional dimensions, Days and StatusReason. Only use the dimension you want your results to group by, ID as I understood.
Stefan,
If this has to be done at the script level, could we not use ApplyMap (This has limitation for 2 fields)
But is it possible?
Thanks!
The straight forward analogon to the suggested chart expressions would be a GROUP BY clause with the same functions:
LOAD
ID,
Maxstring(StartDate) as StartDate,
FirstSortedValue(StatusReason, -StartDate) as StatusReason
FROM ...
GROUP BY ID;
Sunni, if your data shows more than one record with the same max StartDate, the FirstSortedValue() either needs the DISTINCT qualifier or you need to add a small value to the sort weight for tie breaking.
Hi Stefan,
I am still facing issue with days and status Reason,it would be helpful if you can provide a solution with attached sample data.