Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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
@Saravanan_Desingh I was able to get to solution by tweaking the code, thanks for the help
ok sure. Can you please close the thread if your question is answered?