Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!
Clarify the example.
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
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;
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)