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

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

[techvarun]

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]

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]

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]

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]

Hi

PFA

regards

Pradosh

[pradosh_thakur]

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

[marcus_sommer]

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

- Marcus