Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Specialist II
Specialist II

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

Anonymous
Not applicable
Author

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?

marcus_sommer

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

Anonymous
Not applicable
Author

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

marcus_sommer

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

Anonymous
Not applicable
Author

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
Master II
Master II

Hi

PFA

Capture.PNG

regards

Pradosh

Learning never stops.
pradosh_thakur
Master II
Master II

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

Learning never stops.
marcus_sommer

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

- Marcus