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...




      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



      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