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.
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
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
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
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;
Thanks Daniel Andersson, worked a treat !