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
      CountryDEGermany
      CountryNZNew Zealand
      CurrencyGBPBritish Pound
      CurrencyUSDAmerican Dollar
      CurrencyEUREuro
      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.

           

          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

          • Re: Using a Loop to read Mapping Load from Excel
            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

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

              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;