topic Re: Using a Loop to read Mapping Load from Excel in QlikView

Using a Loop to read Mapping Load from Excel

Anonymous — Wed, 13 Dec 2017 09:58:15 GMT

Hi all,

I am trying to use an excel file to create multiple mapping loads. I currently have 30 maps to be made from this single file hence why using loop to make my code more efficient. I have an example below.

Mapping Load Name	Field 1 (i.e. Key)	Field 2 (Mapped Var)
Country	UK	United Kingdom
Country	US	United States
Country	DE	Germany
Country	NZ	New Zealand
Currency	GBP	British Pound
Currency	USD	American Dollar
Currency	EUR	Euro
Currency	NZD	New Zealand Dollar
Currency	DKK	Danish Korona

The desired effect is to have the Mapping Load Name as the title for the mapping load to be used in apply map.

Field one as my key which i map onto and Field two as the new data in my data model.

I have tried various attempts with no luck. Any help would be massively appreciated.

Re: Using a Loop to read Mapping Load from Excel

biirka — Wed, 13 Dec 2017 11:02:29 GMT

Try this ...

FileName depends on name of excel file, sheet name and so on.

Don't need to name the fields in the mapping load, I just did that in order to check that the code worked when doing a normal load.

tmp_Mappings:

LOAD

CONCAT(DISTINCT CHR(39) & [Mapping Load Name] & CHR(39), ',') AS Mapping

;

LOAD

[Mapping Load Name]

FROM FileName

;

LET vMappings = PEEK('Mapping');

DROP TABLE tmp_Mappings;

FOR EACH vMap IN $(vMappings)

$(vMap):

MAPPING

LOAD

[Field 1 (i.e. Key)] AS $(vMap)_Key,

[Field 2 (Mapped Var)] AS $(vMap)_Value

FROM FileName

WHERE

[Mapping Load Name] = '$(vMap)'

;

Re: Using a Loop to read Mapping Load from Excel

marcus_sommer — Wed, 13 Dec 2017 12:18:13 GMT

You could try it with something like this:

MappingRawData:

Load * from excel;

for i = 1 to fieldvaluecount('Mapping Load Name')

let vMappingTable = fieldvalue('Mapping Load Name');

[Map_$(vMappingTable]:

mapping load Field1, Field2 resident MappingRawData

where ['Mapping Load Name] = '$(vMappingTable)';

- Marcus

Re: Using a Loop to read Mapping Load from Excel

vinieme12 — Wed, 13 Dec 2017 12:26:42 GMT

MappingTables:

Load Distinct [Mapping Load Name]

FROM xxxxxxxxxxxxxxxxx;

for i = 0 to NoOfRows('MappingTables')-1

let vMapName = peek('Mapping Load Name',$(i),'MappingTables');

$(vMapName):

LOAD

[Field 1 (i.e. Key)] as $(vMapName),

[Field 2 (Mapped Var)] as $(vMapName)_Value

FROM xxxxxxxxxxxxxxxxx;

where [Mapping Load Name] = '$(vMapName)';

Next i;

Re: Using a Loop to read Mapping Load from Excel

Anonymous — Thu, 14 Dec 2017 15:40:30 GMT

Thanks Daniel Andersson, worked a treat !