1 Reply Latest reply: Aug 23, 2012 9:36 AM by Manoj Kumar Varatharajan RSS

    need dynamic column while loading

      I have on excel which contains the columns as below

       

      Messageno
      naturepriority
      1assistance1
      2correction2
      3evolution3
      4project4

       

      while loading the data i would like another column to be added dynamically with the column nameticket_type and with few conditions like:

           1. if in nature column value is assistance and correction then in column ticket_type fopr corresponding record it should be incident.

           2. if in nature column value is evolution then in column ticket_type fopr corresponding record it should be changemgmt.

           3. if in nature column value is project then in column ticket_type fopr corresponding record it should be project.

       

      After loading the data should be loaded in this way

      Messagenonaturepriorityticket type
      1assistance1incident
      2correction2incident
      3evolution3changemgmt
      4
      project
      4project








       

      Thanks in advance

      Venu

        • Re: need dynamic column while loading
          Manoj Kumar Varatharajan

          Venu, Please find below the script for your current requirement. I hope this helps.

           

          TableXYZ:

           

          LOAD Messageno,

          nature,

          priority,

          if(nature = 'assistance' or nature = 'correction', 'incident',

          if(nature = 'evolution', 'changemgmt',

          if(nature='project','project'))) AS ticket_type

          FROM

          TABLE xyz.qvd;

           

          If you are going to have multiple values, then form an inline table with two fields nature, ticket_type and use applymap in the load script.  (or) You can join the table with ticket_type with your TableXYZ and still get the ticket_type field in your table.