Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
------------------------------------------------
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?
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
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;
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
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]);
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
Hi, there will be a synthetic key if the table name is removed.
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
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
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