topic Re: Define dimension! in QlikView

Define dimension!

qlikviewaf — Thu, 08 Sep 2022 14:50:59 GMT

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!!

Re: Define dimension!

BrunPierre — Fri, 09 Sep 2022 05:13:17 GMT

Clarify the example.

Re: Define dimension!

qlikviewaf — Fri, 09 Sep 2022 07:03:00 GMT

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

Re: Define dimension!

BrunPierre — Fri, 09 Sep 2022 09:44:01 GMT

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;

Re: Define dimension!

Anonymous — Fri, 09 Sep 2022 17:41:41 GMT

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.

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

HTH,

JohnInSD

(aka johncaqc)