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
