Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Vlookup

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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;


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
MVP & Luminary
MVP & Luminary

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;


talk is cheap, supply exceeds demand

View solution in original post

Not applicable

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