Good Morning,
I have run into a situation and after researching peek and other functions, I still can't get this to work.
EDThroughtputTypeMaster:
LOAD MetricType, MetricNumber, MetricAbbreviation, MetricHeader1, MetricHeader2, MetricDataColumn1, MetricDataColumn2, MetricColumn
FROM
Data\EDThroughputTypeMaster.xlsx
(ooxml, embedded labels, table is TypeDim);
I have also loaded rows into a table EDThroughputDtl - which has many columns and metrics in it.
GOAL
Read the TypeMaster:
Then, build a new " MetricTbl " with the "values from the existing table Dtl" using "variables" as column names for existing resident table
If I leave off "PEEK" - then it just puts the "names" in there. That is not correct (highlighted in Red below).
SCRIPT:
Let NumRows = NoOfRows('EDThroughtputTypeMaster');
For i=0 to $(NumRows)-1
Let vNumber = Peek('MetricNumber',$(i),'EDThroughtputTypeMaster');
Let vDtaCol1 = Peek('MetricDataColumn1',$(i),'EDThroughtputTypeMaster');
Let vDtaCol2 = Peek('MetricDataColumn2',$(i),'EDThroughtputTypeMaster');
Let vMetric = Peek('MetricColumn',$(i),'EDThroughtputTypeMaster');
Set TblName = 'EDThroughputDtl';
//Iterate through the fields of table
MetricTbl:
Load
CSN
,$(vNumber) as MetricNumber
,'$(vDtaCol1)' as Date1
,'$(vDtaCol2)' as Date2
,'$(vMetric)' as Metric
Resident '$(TblName)'
Where Len('$(vMetric)' ) > 0
;
NEXT I
It is not working and any guidance will be appreciated.