Using a Loop to read Mapping Load from Excel

[Anonymous]

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.

[biirka · Accepted solution]

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

     ;

NEXT

[marcus_sommer]

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

next

- Marcus

[vinieme12]

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;

[Anonymous]

Thanks Daniel Andersson, worked a treat !