Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I'm trying to find a way to create a field from data located in other fields.
Basicly, form the table below, How can i find the latest value for the item 1 if the message is B.
The result should be 10.
Date | ID item | Message | Value |
25-mai | 1 | B | 10 |
24-mai | 1 | A | 5 |
23-mai | 1 | B | 3 |
22-mai | 1 | B | 7 |
21-mai | 2 | A | 1 |
20-mai | 2 | A | 7 |
19-mai | 2 | C | 6 |
For information, this operation is done in the script. All the values found are then put together in a field in order to easily find them in qlik sense when a ID item is called.
Thank you for your time,
Baptiste
You want to do this in the script? You want to store this in a new column? May be this
Left Join (TableName)
LOAD [ID item],
[Message],
FirstSortedValue(Value, -Date) as Recent_Value
Resident TableName
Group By [ID item], [Message];
Thank you.
After a few tries, This works :
load "ID item"
FirstSortedValue([value],-if([message] like 'B' ,[date]))
As "value of interest"
resident TableName group by "ID item";
Baptiste