topic Re: Using Applymap to multiple excel files in App Development

Using Applymap to multiple excel files

Anonymous — Fri, 08 Dec 2017 04:15:37 GMT

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

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

Re: Using Applymap to multiple excel files

techvarun — Fri, 08 Dec 2017 04:28:48 GMT

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

Re: Using Applymap to multiple excel files

Anonymous — Fri, 08 Dec 2017 06:25:16 GMT

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

marcus_sommer — Fri, 08 Dec 2017 08:07:42 GMT

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

Re: Using Applymap to multiple excel files

Anonymous — Fri, 08 Dec 2017 09:58:51 GMT

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

marcus_sommer — Fri, 08 Dec 2017 12:13:33 GMT

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

- Marcus

Re: Using Applymap to multiple excel files

Anonymous — Mon, 11 Dec 2017 02:10:50 GMT

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?

Re: Using Applymap to multiple excel files

pradosh_thakur — Mon, 11 Dec 2017 06:25:42 GMT

PFA

regards

Pradosh

Re: Using Applymap to multiple excel files

pradosh_thakur — Mon, 11 Dec 2017 06:27:49 GMT

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

marcus_sommer — Mon, 11 Dec 2017 07:38:03 GMT

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

- Marcus

Re: Using Applymap to multiple excel files

Anonymous — Wed, 13 Dec 2017 02:04:25 GMT

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?

Re: Using Applymap to multiple excel files

pradosh_thakur — Wed, 13 Dec 2017 03:31:08 GMT

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

Re: Using Applymap to multiple excel files

Anonymous — Wed, 13 Dec 2017 06:34:17 GMT

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;

Re: Using Applymap to multiple excel files

pradosh_thakur — Wed, 13 Dec 2017 06:48:07 GMT

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

Re: Using Applymap to multiple excel files

Anonymous — Wed, 13 Dec 2017 07:02:20 GMT

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

Re: Using Applymap to multiple excel files

pradosh_thakur — Wed, 13 Dec 2017 08:59:57 GMT

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

Re: Using Applymap to multiple excel files

Anonymous — Wed, 13 Dec 2017 09:11:52 GMT

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

Re: Using Applymap to multiple excel files

pradosh_thakur — Wed, 13 Dec 2017 09:19:54 GMT

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

Re: Using Applymap to multiple excel files

Anonymous — Wed, 13 Dec 2017 09:33:31 GMT

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

Re: Using Applymap to multiple excel files

pradosh_thakur — Wed, 13 Dec 2017 09:57:54 GMT

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