Vlookup

[#1]

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.

Please suggest! I have attached the excel file

[Gysbert_Wassenaar · Accepted solution]

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,

...etc...

max(UDateTime) as LastDateTime

from ...somewhere...

group by OBJECT_ID;

[#3]

Thanks! I will buzz you again If I encounter any problem