Discussion board where members can get started with QlikView.
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 MyBlueFROM[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 DistinctManufacturer ,floor(rand()*255) AS MyRed,floor(rand()*255) as MyGreen,floor(rand()*255) as MyBlueresident TRANSACTIONSwhere 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)