8 Replies Latest reply: Feb 5, 2018 9:45 AM by Jahanzeb Hashmi RSS

    Creating a new column in the script

    colin odonnell

      Hello there,

       

      I currently have a table like the following:

       

      ProductLetterValues
      MilkA1
      MilkB2
      MilkC3
      ButterD4
      ButterA4
      ButterB5
      CheeseC6
      CheeseD6

       

      I want to convert it to the following structure from the script,

      namely move the repeating values in the Letter column to 4 individual columns and transfer the values according.

      This would also mean the Letter column is removed.

       

      ProductABCD
      Milk123
      Butter45 4
      Beer
      Cheese 66

       

      I have tried both the Generic Load (with an additional script to consolidate the generic tables taken from here Sample Generic Load Script) but what is happening is that extra rows are being generated as follows:

       

      ProductLetterValuesABCD
      MilkA11
      MilkB20
      MilkC30
      ButterD40
      ButterA44
      ButterB50
      CheeseC60
      CheeseD60
      MilkA10
      MilkB2 2
      MilkC3 0
      ButterD4 0
      ButterA4 0
      ButterB5 5
      CheeseC6 0
      CheeseD6 0

       

      I have also tried using If statements and calculated fields instead of a Generic Load but with the same effect.

       

      Help!

       

      Thanks,

      Colin