Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

Community Browser