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?
