2 Replies Latest reply: Feb 19, 2015 6:31 AM by Brian Booden RSS

    Question on Applymap

      Hey Guys,

       

      Relatively new to Qlikview. I am trying to simply multiple the [Gross Amount] and the [Multipler] from two different tables but this loadscript is resulting in my original table rows being duplicated. Any ideas?

       

       

      Table_2:
      LOAD
      Country & CATEGORY_NAME as [Key Indicator],
      [Multiplier + Percentage]  as [Multiplier]

      FROM
      restricted

      TableMapping:
      Mapping
      LOAD
      [Key Indicator],
      [Multiplier]
      Resident Table_2;


      Table_1:
      LOAD
      [GROSS AMOUNT],
      [GROSS RECLAIM AMOUNT],
      [ISO COUNTRY CODE] & [CATEGORY NAME]as [Key Indicator]
      FROM
      restricted


      TestApplyMap:
      Load
      [GROSS AMOUNT],
      [Key Indicator],
      [GROSS AMOUNT]*(ApplyMap('TableMapping',[Key Indicator],'missing')) as Multiplier
      Resident Table_1;





        • Re: Question on Applymap
          Miguel Angel Baeyens de Arce

          Hi,

           

          At the end of the script add

           

          DROP TABLE Table_1;

           

          An implicit concatenation always happens in Qlik whenever two or more tables have the same number of fields and these fields are named alike.

          Miguel

           

          EDIT: FWIW, You can save one LOAD step by doing

           

          Table_1:
          LOAD 
          [GROSS AMOUNT], 
          [GROSS RECLAIM AMOUNT], 
          [GROSS AMOUNT]*(ApplyMap('TableMapping',[ISO COUNTRY CODE] & [CATEGORY NAME],'missing')) as Multiplier
          FROM
          restricted;
          

           

          You can use expressions as parameters for ApplyMap() as well instead of first generating the composite key and after applying it.

          • Re: Question on Applymap
            Brian Booden

            Hi,

             

            After doing a Resident Load, most of the time, you will want to drop the original table, as Resident creates another copy of the table in memory.

             

            Try adding the statement:

             

            Drop Table Table_1;


            Regards,

            Brian