topic line charts based on listbox data in QlikView

line charts based on listbox data

Thu, 02 Jul 2009 17:57:43 GMT

Hello Fellow Users,

I have the following script (see below) and I would like to create Line Charts based on each ProductDescription. Can someone help me how I can create something like a bookmark in a graph? I tried this (=ProductDescription = 'CT-2') in the dimension field but it didn't work.

Regards,

Aissam

-----------------------------------------------------------------------------------------------------------------------------------------

TempJobLines:

Sql

Select

JobNumber as JOB_NUMBER,

ProductCode,

CUSTOMER_NUMBER,

PRODUCT_LINE,

jl.Description as Joblines_Description,

Quantity

from Jobscope.dbo.JobLineitems jl

join jobscope.dbo.ipjobm jm on jm.JOB_NUMBER = jl.JOBNUMBER

where Jobnumber < 'A';

//------------------------------------------------------------------------------------

JobLines:

load *,

IF(mid(ProductCode,1,2) = '00', 'CT-2',

IF(mid(ProductCode,1,2) = '01', 'CT-25',

IF(mid(ProductCode,1,2) = '04', 'MS-50',

IF(mid(ProductCode,1,2) = '05', 'CT-3',

IF(mid(ProductCode,1,2) = '09', 'MT-10',

IF(mid(ProductCode,1,2) = '11', 'SPIRAAL TRANSPORTEUR',

IF(mid(ProductCode,1,2) = '25', 'ONDERDELEN',

IF(mid(ProductCode,1,2) = '26', 'BANDDELEN',

IF(mid(ProductCode,1,2) = '28', 'E-KASTEN',

IF(mid(ProductCode,1,2) = '32', 'DOCUMENTATIE',

IF(mid(ProductCode,1,2) = '37', 'ROLLENTRAPO',

IF(mid(ProductCode,1,2) = '38', 'MAGNETIC SCREW CONVEYOR',

IF(mid(ProductCode,1,2) = '42', 'WANNERPOMP/HOOD',

IF(mid(ProductCode,1,2) = '52', 'CT-20',

IF(mid(ProductCode,1,2) = '70', 'AFMONTEREN',

IF(CUSTOMER_NUMBER = '170132' AND mid(ProductCode,1,2) = '24','RBF FILTERS UNISIGN',

IF(CUSTOMER_NUMBER = '140342' AND mid(ProductCode,1,2) = '24','RBF FILTERS HURON',

IF(mid(ProductCode,1,2) = '23' AND INDEX(Joblines_Description,'HP') > 0, 'HP TANK',

IF(mid(ProductCode,1,2) = '23' AND INDEX(Joblines_Description,'BEHAELTER') > 0, 'TANK',

IF(mid(ProductCode,1,2) = '23' AND INDEX(Joblines_Description,'TANK') > 0,'COOLANT TANK',

IF(ProductCode = '51000','CONSEP2000 CS-1',

IF(ProductCode = '51001','CONSEP2000 125HP',

IF(ProductCode = '51002','CONSEP2000 200HP','OTHER'))))))))))))))))))))))) as ProductDescription

Resident TempJobLines;

Drop table TempJobLines;

line charts based on listbox data

Fri, 03 Jul 2009 12:58:08 GMT

Hi,

Not sure if I understood this problem correctly. Do you want to create a chart which has separate line for each product? Also, are there any other dimensions in this chart?

Thanks

Amit

line charts based on listbox data

Fri, 03 Jul 2009 14:21:09 GMT

Hello,

I think I was a little bit obscure about the problem. It took me a few hours but I solved it. I have a 2 tables one has the hours and one has the ProductDescription. The different products are in the field ProductDescription. I wanted to select one product per graph. I did this eventually in the dimension field with the following statement =If (ProductDescription = 'CT-20','CT-20').

Regards,

Aissam

line charts based on listbox data

Oleg_Troyansky — Sat, 04 Jul 2009 00:30:42 GMT

Aissam,

I think you are using too many IF statements.

1. I'd highly recommend replacing your nested IF statement in the load script with a Mapping table that you could load from Excel. What are you going to do if you need to add another Product?

2. In order to get a line per Product, you need to use ProductDescription as your Dimension - as simple as that, no IF statements needed.

cheers,

Oleg