Multiple Dynamic Subject Lines

[Eunice]

I have a table that have two subject lines, the first line is the categories of the subjects, and the second line is the detail subjects. It looks like as below:

Key	Key	Key	Key	History	History	Forecast	Forecast
Product	Location	Customer	Demand Type	Aug2024	Sep2024	Oct2024	Nov2024
ABC	France	xyz	Ordered	600	200	667	667

As time pass by, the table will grow wider, because first column in "Forecast" category will move to be the last column under "History" category, as below.

Key	Key	Key	Key	History	History	History	Forecast
Product	Location	Customer	Demand Type	Aug2024	Sep2024	Oct2024	Nov2024
ABC	France	xyz	Ordered	600	200	667	667

How can I load the data under the "Forecast" category while this table automatically grow month by month?

[Qrishna]

Try Below:

//Prepare your Customer details Table
Data:
LOAD * INLINE [
Group,Field, FieldValue
Key, Product, ABC
Key, Location, France
Key, Customer, xyz
Key, Demand Type, Ordered
];

//Create a unique key for a customer
left join(Data)
TempData:
Load
Group,
Concat(FieldValue, '-') as %Key // Concatenate FieldValue by Group with a '-' separator
Resident Data
Group By Group;

// This is your Value/Metrics table
// assuming this is the table that gets updated/added every month,
// if you have seperate tables for each month's data, load all them using for-loop and concatenate them, check community on how to load multiple excel files/database files
concatenate(Data)
Load %Key,
Month(Month_Start_Date) & Year(Month_Start_Date) as Field,
if(Month_Start_Date >= today(), 'Forecast', 'History') as Group,
Month(Month_Start_Date) & Year(Month_Start_Date) as Month_Year,
Value as FieldValue ;
Load Product & '-' & Location & '-' & [Demand Type] & '-' & Customer as %Key,
Date(MonthStart(Date#(Month_Start_Date, 'DD-MMM-YYYY')), 'DD-MMM-YYYY') as Month_Start_Date,
Value
;
LOAD * INLINE [
Product,Location,Demand Type,Customer, Month_Start_Date, Value
ABC,France,Ordered,xyz,01-AUG-2024,600
ABC,France,Ordered,xyz,01-SEP-2024,200
ABC,France,Ordered,xyz,01-OCT-2024,667
ABC,France,Ordered,xyz,01-NOV-2024,667
];

//create a seperate table that store the pivoted view values
Dat1:
LOAD DISTINCT
%Key
RESIDENT Data;

//Split the rows
for i = 0 to NoOfRows('Data') - 1

Let vFieldName = Peek('Field', i, 'Data');
Let vFieldValue = Peek('FieldValue', i, 'Data');
Let vGroup = Peek('Group', i, 'Data');

left join(Dat1)
LOAD
'$(vFieldValue)' as [$(vFieldName)]
AutoGenerate 1 ;

next;

UI:

CReate a pivot table with dimensions: Group, Field and sort by load orders

Expression: FieldValue