Using Applymap to multiple excel files

[Anonymous]

Hi, i'm looking to map columns from one excel file to multiple excel files in a 'For Loop' using the Applymap() function. Is there an example of how this could be achieved?

E.g:

Objective End Result:

My code now for the Reports is something like:

------------------------------------------------

FOR Each File1 in filelist ('lib://ExcelFiles/1 Templates\Report 2017*.xlsx')

SumReportOct:

  LOAD

  "ExtractedValue",

  "ReturnedClusterName"

  "Compliance Score"

FROM '$(File1)' (ooxml, embedded labels, table is [Overall_Score]);

NEXT;

------------------------------------------------

My code now for the Asset List is something like:

------------------------------------------------

ClusterToClusterMapping:

Mapping Load

"ReturnedClusterName",

"Compliance Score"

Resident SumReportOct;

AssetListComplianceScoreHostZ:

Mapping Load Distinct

"Extractedvalue",

"Compliance Score"

Resident SumReportOct;

AssetList1:

load*,

ApplyMap('AssetListComplianceScoreHostZ',"HostNameFinal",ApplyMap('AssetListComplianceScoreHostZ',"Cluster Name"

,ApplyMap('ClusterToClusterMapping',"Cluster Name",'Unknown'))) as "Compliance Score Final"

;

LOAD

"HostNameFinal" as "HostNameFinal"

"Cluster Name" as "Cluster Name"

FROM [lib://ExcelFiles/1 Templates\Asset Data October 2017.xlsx](ooxml, embedded labels, table is Server);

------------------------------------------------

[Anonymous]

Hi, because the question that i've posted is just a scaled down example to what i am really doing. Your solution would achieve the same results, i would be able to find all of the HostNameFinal that exists along with the extractedvalue, but with what i am really working on, the HostNameFinal has HostNames that could not be found in the extractedvalue column and they have to be labeled to have an compliance score of 0.

So i am assuming that's why i have to start by mapping the HostNameFinal to the ExtractedValue so that i can deal with the rest of the HostNameFinal that are not found.

[marcus_sommer]

Normally it's just the opposite what's needed - filling/cleaning/replacing values from dimension-tables to fact-tables and not reverse - therefore my suggestion.

If the "Asset List" is not a dimension-table there may other ways to build a datamodel more useful maybe by just concatenating both tables and applying the cleaning-mapping if necessary on them.

In your case I would probably keep my approach and if it's really needed to have all values from the "Asset List" I would just add them by a concatenating like this:

concatenate(final)

load HostNameFinal as ExtractedValue2, 0 as [Compliance Score]

FROM [lib://ExcelFiles/1 Templates\Asset Data October 2017.xlsx](ooxml, embedded labels, table is Server)

where not exists(ExtractedValue2, HostNameFinal);

- Marcus

[Anonymous]

It seems like the subfield and filename() is not working properly,

This is how my reports look like.

[pradosh_thakur]

Try this 

subfield(Right(filename(),11) ,'.') as Month

or

subfield(Right(filename(),11) ,'.',1) as Month

[Anonymous]

Hi, i've used your subfield solution, but it seems like it still isn't working.

Here's my current code:

[dummy]:

LOAD * INLINE [

    ExtractedValue, Compliance Score

    dummy, dummy

];

FOR Each File in filelist ('lib://Qlik Loop/Report 20*.xlsx')

[Reports]:

NoConcatenate

LOAD

    ExtractedValue,

    "Compliance Score (%)" as "Compliance Score"

    ,subfield(Right(filename(),11),'.',1) as Month

FROM '$(File)' (ooxml, embedded labels, table is [Overall]);

NEXT;

for i = NoOfTables() - 1 to 0 step - 1

    let vTable = tablename($(i));

IF WildMatch('$(vTable)','Report*') then

Set vMonth= date(date#(num(KEEPCHAR('$(vTable)','0123456789')),'YYYYMM'),'MMM-YY');

[Asset]:

Load *,

//  $(vMonth) as "Month1",

   alt(Lookup('Compliance Score','ExtractedValue', HostNameFinal, '$(vTable)'),

   Lookup('Compliance Score','ExtractedValue', ClusterName, '$(vTable)')) as Final,

   alt(Lookup('Month1','ExtractedValue', HostNameFinal, '$(vTable)'),

Lookup('Month1','ExtractedValue', ClusterName, '$(vTable)')) as Month;

  //Right('$(vTable)',6) as Month

LOAD

  "HostNameFinal",

  "ClusterName"

FROM [lib://Qlik Loop/Asset List.xlsx](ooxml, embedded labels, table is Asset);

drop table '$(vTable)';

endif

next i;

drop table dummy;

[pradosh_thakur]

Hi Stanley

you have to tweak your code a little to get the exact thing you wanted. I cant see your file name neither i have seen your data. You have to try from your side too if you want to get the answers.

please upload the three files with dummy data may be if you want me to give the exact code. Whatever i can give right now is just a guess as i cant tell how your file name looks like ....

regards

Pradosh