I think I should be using ApplyMap?

bobbydave — Thu, 02 Apr 2015 09:10:15 GMT

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

topic I think I should be using ApplyMap? in QlikView

I think I should be using ApplyMap?