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 thanks for suggesting this solution, but how make it such in a way that it loops through all of the reports in the directory?

[pradosh_thakur]

You can use a for loop to load all the file with 'Report *'  than use the below loop to map them to Asset list table.

regards

Pradosh

[Anonymous]

Right, if i do a for loop for all of the reports, how would i be able to retrieve the date then?

Here's my current code:

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

[Reports]:

LOAD

    ExtractedValue,

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

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

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;

NEXT;

[pradosh_thakur]

Try this

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"

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

   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

[Anonymous]

Hi, i'm getting this as an result to your codes:

It seems like the month is getting it's fields based on the table name that was created in the data load editor.

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

[Reports]: <-------- From here

NoConcatenate

LOAD

    ExtractedValue,

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

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

[pradosh_thakur]

Try this

dummy:

load * inline[

ExtractedValue, Compliance Score

dummy, dummy

];

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

NoConcatenate

LOAD

    ExtractedValue,

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

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

   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

[Anonymous]

Hi, there will be a synthetic key if the table name is removed.

[pradosh_thakur]

You don't really need those table other than mapping right . Synthetic keys are not always harmful. if you don't want synthetic keys than  try using drop table dummy; at the last. if possible show me your data model.

regards

Pradosh

[Anonymous]

Hi, the synthetic key still appears even after dropping the dummy table or commenting the entire table out. Here's a picture of my data model

[pradosh_thakur]

Thats why i asked you to tweak your code.. please notice that you are using table name which is overall nor report*

dummy:

load * inline[

ExtractedValue, Compliance Score , Month

dummy, dummy, dummy

];

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

NoConcatenate

LOAD

    ExtractedValue,

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

    subfield(Right((filename()',10),'.') 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)','Overall*') then

   Load *,

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

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

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

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

INLINE [

    HostNameFinal, ClusterName

    BIEW01, BIEW01C

    ARKW01V, ARKW01C

    CRMAW01V, CRMAW01C

];

drop table '$(vTable)';

endif

next i;

regards

Pradosh