Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

stanley123
New Contributor

Using Applymap to multiple excel files

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:

Multiple Mapping.PNG

Objective End Result:

EndResult.PNG



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);

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

25 Replies
techvarun
Valued Contributor II

Re: Using Applymap to multiple excel files

I beleive the Report_201705 is your excel file or QVD name?

in this case use something like below to achieve the result

Load

[Host Name],

[Compliance Score],

Right(Filebasename(),5) as Month

Report*.qvd(qvd);

stanley123
New Contributor

Re: Using Applymap to multiple excel files

Report_201705 would be the excel. Thanks for suggesting on how to take the first 5 characters from the right of the filename. But how can i do an Applymap() function to map the row "Host Name" in my asset list to all of the Reports and get the compliance score for each month?

Re: Using Applymap to multiple excel files

I think you don't need an applymap() here just loading (maybe per explicit concatenate-statement) the files will be enough by adding the period like suggested from Varun.

- Marcus

stanley123
New Contributor

Re: Using Applymap to multiple excel files

Hi, i have updated my question, hopefully it would be better to understand why there's a need for ApplyMap().

Re: Using Applymap to multiple excel files

I think I would do something like this:

mapHost: mapping load HostNameFinal, [Cluster Name]

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

mapCluster: mapping load [Cluster Name], HostNameFinal

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

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

     final:

     load

          ExtractedValue, [Compliance Score], right(filebasename(), 6) as YearMonth,

          applymap('mapHost', ExtractedValue,

               applymap('mapCluster', ExtractedValue, ExtractedValue)) as ExtractedValue2

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

next

- Marcus

stanley123
New Contributor

Re: Using Applymap to multiple excel files

Hi, thanks for suggesting this solution, but my base has to start by mapping the HostNameFinal to the ExtractedValue. Are there any other ways to go around this?

pradosh_thakur
Valued Contributor III

Re: Using Applymap to multiple excel files

Hi

PFA

Capture.PNG

regards

Pradosh

pradosh_thakur
Valued Contributor III

Re: Using Applymap to multiple excel files

this is the code. Change the code a bit by replacing inline load with your table.

[Report 201705]:

LOAD * INLINE [

    ExtractedValue, Compliance Score

    BIEW01, 100

    ARKW01V, 98

    CRMAW01C, 95

];

NoConcatenate

[Report 201706]:

LOAD * INLINE [

    ExtractedValue, Compliance Score

    BIEW01C, 95

    ARKW01V, 100

    CRMAW01V, 97

];

NoConcatenate

[Report 201707]:

LOAD * INLINE [

    ExtractedValue, Compliance Score

    BIEW01, 98

    ARKW01C, 99

    CRMAW01V, 91

];

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

    let vTable = tablename($(i));

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

   Load *, alt(Lookup('Compliance Score','ExtractedValue', HostNameFinal, '$(vTable)'),Lookup('Compliance Score','ExtractedValue', ClusterName, '$(vTable)')) as Final,

   Right('$(vTable)',6) as Month INLINE [

    HostNameFinal, ClusterName

    BIEW01, BIEW01C

    ARKW01V, ARKW01C

    CRMAW01V, CRMAW01C

];

drop table '$(vTable)';

endif

next i;

regards

Pradosh

Re: Using Applymap to multiple excel files

Why? Did you try my suggestion and it really differ from your expected results? What are these differences?

- Marcus