Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I’m working on code to transform data coming out of the “DataIntegrity” table, which is reading from an Excel spreadsheet, into a QVD. The spreadsheet has one metric record per week (in format YYYY-MM-DD), one column per metric. The resulting QVD will have a row per metric (“Item Branch”, “BOM Accuracy”, “Router Accuracy”, or “Work Center Accuracy”) and a column per month of the year in format “YYYY-MM” (e.g. 2022-01, 2022-02, etc.) storing the average for the recorded values for that month. My challenge at the moment is that, when I am creating the column at runtime, it is coming up as blank, I get the following message:
Blank field name not allowed
MasterData_ItemBranch_KPI:
LOAD
'SNN' as Site,
'Master Data' as Pillar,
'Item Branch Master' as MetricName,
'Supply Chain Scorecard' as SourceApp,
Avg([Item Branch]) as []
Resident
DataIntegrity
Where
Year(%AccuracyDate) >= '2021'
Group by
%AccuracyDate
The code below might not be a 100% yet, but right now I’m focusing on the column name. I saw this post, but I think I’m not reflecting it right. The idea is that the average column name is generated at runtime:
[DataIntegrity]:
//CrossTable(Groups, Data, 1)
LOAD
Date as %AccuracyDate,
[Item Branch],
[BOM Accuracy],
[Router Accuracy],
[Work Center Accuracy]
FROM
[15.MasterDataAccuracy.xlsx]
(ooxml, embedded labels, table is Sheet1);
// TO DO: Convert to the same format as the metrics file
// Do for Shannon and Galway for all four metrics
MasterData_ItemBranch_KPI:
LOAD
'SNN' as Site,
'Master Data' as Pillar,
'Item Branch Master' as MetricName,
'Supply Chain Scorecard' as SourceApp,
Avg([Item Branch]) as [$(Text(Year(%AccuracyDate)) & chr(45) & Text(num(Month(%AccuracyDate))))]
Resident
DataIntegrity
Where
Year(%AccuracyDate) >= '2021'
Group by
%AccuracyDate
;
//store InventoryTurnsKPI_header into [$(varQVDFilePath)InventoryTurnsKPI.qvd](qvd);
store MasterData_ItemBranch_KPI into [$(varQVDFilePath)SC_Scorecard_KPI_MDSNNIM.qvd](qvd);
Can you please help me figure out how to produce the column name within the script?