Define dimension!

[qlikviewaf]

Hi, i have a database on qlikview where there are several data by days.

I would like to define a dimension based on the most recent value. 

Example: for the combination of KIT/LOT i would like to define a dimension that should be equal to the last value available in the column "Location". How this can be done ? 

THank you!!

[BrunPierre]

Clarify the example.

[qlikviewaf]

I would like for a combination of KIT and Lot, to have the "dimension to define" always equal to the most recent value available on the field "Location". 

For the KIT A Lot 12 - the most recent value is related to date 04/01/2022 and the Location is equal to warehouse - so the location to be defined should be always warehouse.

let me know if not clear,

Thank you

[BrunPierre]

Something like this

Data:
LOAD * Inline[
Date,Kit,Lot,Location
01/01/2022,A,12,Consignment
03/01/2022,A,12,Warehouse
04/01/2022,A,12,Warehouse
01/01/2022,Z,874,Consignment
03/01/2022,Z,874,Consignment
04/01/2022,Z,874,Consignment];

Left Join(Data)
LOAD Kit,
     Lot,
Maxstring(Location) as DimensionToDefine,
Max(Date(Date)) as Date

Resident Data
Group By Kit,Lot;

[Anonymous]

I think this will do it...

After you load your data, use FirstSortedValue with the sort_weight being "-Date" and join via Kit & Lot. You may need to sort by Kit, Lot and Date ascending if they aren't already sorted by those fields.

Data:
LOAD * Inline[
Date,Kit,Lot,Location
01/01/2022,A,12,Consignment
03/01/2022,A,12,Warehouse
04/01/2022,A,12,Warehouse
01/01/2022,Z,874,Consignment
03/01/2022,Z,874,Consignment
04/01/2022,Z,874,Consignment];

Latest:
Left Join(Data)
LOAD
Kit,
Lot,
FirstSortedValue(Location,-Date) as DimensionToDefine
Resident Data
group by Kit,Lot
;

HTH,

JohnInSD

(aka johncaqc)