I am working on Complain Center Dashboard. I have 1.6 million unique object IDs. What I want is to keep the original data and create a straight table in which I want to represent object ID information of latest status only.
I used object_ID as dimension and expression aggr(max(UDateTime),OBJECT_ID) to get the latest status update time but now I want to get corresponding information to it that is mostly text. I have attached one text field that is ‘status’ there are others too. I want ideas to optimize my memory and processing as there are around 8 more fields and all of them are text. One thing I want to share is that it is least likely but possible that there are multiple status with same time.
Moreover I would like to add an expression to calculate the time of ticket created till it is completed or till today in the same straight table.
You can use the firstsortedvalue function: firstsortedvalue(Status, -UDateTime). For other fields you can replace Status with the other field name. If you want you can also calculate this in the script:
load OBJECT_ID,
firstsortedvalue(Status, -UDateTime) as LastStatus,
firstsortedvalue(OtherField, -UDateTime) as LastOtherField,
You can use the firstsortedvalue function: firstsortedvalue(Status, -UDateTime). For other fields you can replace Status with the other field name. If you want you can also calculate this in the script:
load OBJECT_ID,
firstsortedvalue(Status, -UDateTime) as LastStatus,
firstsortedvalue(OtherField, -UDateTime) as LastOtherField,
