topic Re: look up max() and get corresponding value in QlikView

look up max() and get corresponding value

DM106 — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

The table that i use has 5 dimensions , i need to pick max(period) and its corresponding value and create an entry of it with hardcoded period 2020012 retaining the other dimensions.

So for Eg. in the attached Document

I must check for max(Period) for all combinations of Dimensions and pick its value

So for Name CYTD, Key1 7BS44, Type Business, max(Period) is 202003, so i must look up for its value which is 11 and add an entry in the table with Name CYTD, Key1 7BS44, Type Business, Period 2020012(hardcoded), Value 11

This must be repeated across all combinations of the dimensions

So can someone help me get this done on the Script

Re: look up max() and get corresponding value

Saravanan_Desingh — Tue, 05 May 2020 19:39:43 GMT

Are you looking like this?

tab1: LOAD Key1, [KEY 2], Name, Type, Value, Period FROM [C:\Users\sarav\Downloads\Grp-FY-v2.xlsx] (ooxml, embedded labels, table is Sheet1); Left Join LOAD Key1, Max(Period) As Max_Period ,FirstSortedValue(Value, -Period+Value) As Min_Value Resident tab1 Group By Key1;

Re: look up max() and get corresponding value

DM106 — Wed, 06 May 2020 04:27:23 GMT

No thats not the expectation,

We will need to join across all combinations of Dimensions get the value against max(Period) and then create a seperate entry for it

For eg.

the result for CYTD and PMQ with Type Business and Key1 7BS44, Key 2 Blank i must see below records

7BS44	CYTD	Business	90	2020001
7BS44	CYTD	Business	25	2020002
7BS44	CYTD	Business	11	2020003
7BS44	CYTD	Business	11	2020012
7BS44	PMQ	Business	21	2020003
7BS44	PMQ	Business	23	2020001
7BS44	PMQ	Business	89	2020002
7BS44	PMQ	Business	21	2020012

Similarly for CYTD and PMQ with Type All and Key1 and Key 2 Blank, the records should be:

CYTD	All	19	2020001
CYTD	All	85	2020002
CYTD	All	9	2020003
CYTD	All	9	2020012
PMQ	All	96	2020003
PMQ	All	76	2020001
PMQ	All	67	2020002
PMQ	All	96	2020012

Note: The new entry is highlighted in Bold

The logic must flow through likewise for all combinations.

Hope its clear now

Re: look up max() and get corresponding value

DM106 — Wed, 06 May 2020 09:00:48 GMT

@Saravanan_Desingh I was able to get to solution by tweaking the code, thanks for the help

Re: look up max() and get corresponding value

Saravanan_Desingh — Wed, 06 May 2020 13:00:47 GMT

ok sure. Can you please close the thread if your question is answered?