4 Replies Latest reply: Dec 14, 2017 10:40 AM by Matthew Davies RSS

    Using a Loop to read Mapping Load from Excel

    Matthew Davies

      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 NameField 1 (i.e. Key)Field 2 (Mapped Var)
      CountryUKUnited Kingdom
      CountryUSUnited States
      CountryNZNew Zealand
      CurrencyGBPBritish Pound
      CurrencyUSDAmerican Dollar
      CurrencyNZDNew Zealand Dollar
      CurrencyDKKDanish 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
          Daniel Andersson

          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.




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



               [Mapping Load Name]

          FROM FileName



          LET vMappings = PEEK('Mapping');


          DROP TABLE tmp_Mappings;


          FOR EACH vMap IN $(vMappings)






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

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


               FROM FileName


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




          • Re: Using a Loop to read Mapping Load from Excel
            Marcus Sommer

            You could try it with something like this:



            Load * from excel;


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

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


                 mapping load Field1, Field2 resident MappingRawData

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



            - Marcus

            • Re: Using a Loop to read Mapping Load from Excel
              Vineeth Pujari


              Load Distinct [Mapping Load Name]



              FROM xxxxxxxxxxxxxxxxx;



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

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




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

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


              FROM xxxxxxxxxxxxxxxxx;

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



              Next i;