Not exists

Wed, 11 Jul 2012 11:25:24 GMT

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

topic Not exists in QlikView

Not exists