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,
