How To : get a value in a field according to values in other fields

[baptiste_genin]

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

[sunny_talwar]

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];

[baptiste_genin]

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