0 Replies Latest reply: Jul 11, 2012 8:10 AM by lornalouw RSS

    Not exists

      I need assistance with this code please...

       

      I have a Transaction table that contains, amongst others, many Manufacturers.

       

      I also have a table, Manufacturer_Colours, that I have colours per manufacturer.

       

      Now, If a new Manufacturer gets added, I want to add a random colour to that manufacturer. So what I do is I

      compare the Manufacturer table to the Manufacturer_Colours table, if there is a new transaction (thus Manufacturer), it must add a record

      to the _Colours table.

       

      My code looks like this...

       

      1) I read my Manufacturer_colours table to get existing manufacturers and their colours...

       

      "MANUFACTURER COLOURS":

       

      LOAD upper(Manufacturer) as Manufacturer,

          
      upper(Manufacturer) as ManufacturerColour_Exists,

          
      [Manufacturer Colour] AS ManufacturerColour,

          
      KeepChar(SubField([Manufacturer Colour],',',1),'1234567890') as MyRed,

          
      keepchar(SubField([Manufacturer Colour],',',2),'1234567890') as MyGreen,

          
      keepchar(SubField([Manufacturer Colour],',',3),'1234567890') as MyBlue

      FROM

      [Colours.xlsx]

      (
      ooxml, embedded labels, table is Sheet1);



      Then I try to Concatenate any new Manufacturers with a newly 'created' colour.

      Concatenate("MANUFACTURER COLOURS")

      load Distinct

      Manufacturer ,

      floor(rand()*255) AS MyRed,

      floor(rand()*255) as MyGreen,

      floor(rand()*255) as MyBlue



      resident TRANSACTIONS

      where not exists(ManufacturerColour_Exists,Manufacturer);

       

      This code does not 'pick up' any new records...what am I doing wrong??

       

      Note (in Transactions table, the Manufacturer exists in Uppercase already)

       

      Thank you