So ...
I have an Scorecard excel file with ColumnName, Column2, Column3, Value
In another excel file, I have 2 tabs, CompletenessFlag and ActField which have ColumnA and ColumnB and then Column X and Column Z respectively.
So I want to
- Look at ColumnName in my Nulls excel file, take value 1 and then
- in my other excel file, in the CompletenessFlag tab, Iterate each field of ColumnA and check to see if if the value I have from field 1 equals the value of ColumnA from the CompletenessFlag
- if it doesnt, exit and go to value 2 of Column Name in Nulls
- If this value does match, then go to ActField tab, check to see if ColumnX matches also with the previous values that we collected and ALSO that columnZ = 'y'
- if it doesnt, exit and go to value 2 of Column Name in Nulls.
So if you can imagine, in Column Name in my Nulls table, I want to check to see if Column Name = Scorecard, it will then check CompletenessFlag tabe and see if Column Name = Scorecard, if its not, go back to Nulls, and go to the next field and go with the next value. If it does equal Scorecard, then check then next Act_Materialfields tab, is scorecard here? Yes ... but is Score= 'Y'? It is?, Perfect, go back to Nulls and take the value of Count Non-Nulls.
Act_Materialfields_Map:
Mapping
Load
[Table Name] ,
[Column Name]
FROM
(ooxml, embedded labels, table is Act_MaterialFields);
CompletenessFlag_Map:
Mapping
LOAD
[Column Name],
Score
FROM
(ooxml, embedded labels, table is CompletenessFlag);
Nulls:
LOAD
Country & '-' & NULLS_Date as %Nulls_Detail,
Country as Nulls.Country,
[Table Name],
[Column Name],
[Count Null],
[Count non-Null],
ApplyMap('Act_Materialfields_Map', [Table Name], 'error') as [Act_Column_Name] ,
ApplyMap('CompletenessFlag_Map', [Column Name], 'error') as Comp_Column_Name,
NULLS_Date,
NULLS_Country_Code
FROM
(qvd);
Any help appreciated
