look up max() and get corresponding value

[DM106]

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 

[Saravanan_Desingh · Accepted solution]

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;

[DM106]

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

[DM106]

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

[Saravanan_Desingh]

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