topic QV Data modeling in QlikView

QV Data modeling

guya — Tue, 08 Dec 2009 15:20:29 GMT

Hi all,

I need to create a calculated dimension or table (Weight Category) based on thousand and thousand different data in the weight table.

I got million of rows in the weight table, and the weight data goes fromt 0.1 till 9999. I would like to have a consolidated table like weight category 0>1kg, 1-2kg, 2-5kg, 5-10kg, 10-50kg, >50kg that can be use as dimension.

If you could please help me with the above, it would be most appreciated.

Many thanks,

Guy

QV Data modeling

Tue, 08 Dec 2009 15:32:34 GMT

Use CASE Statement in the query itself.

like CASE WHEN Weight >0 and weight < 1 then '< 1kg'

WHEN Weight >= 1 and weight < 2 then '1-2 kg' . etc

-Raghu.

QV Data modeling

guya — Tue, 08 Dec 2009 18:33:28 GMT

Hello Raghu,

Do I need to include it in the script when loading the table?

Thx

Guy

QV Data modeling

Tue, 08 Dec 2009 18:54:34 GMT

2 ways to do

1) Include the CASE statement in the query itself

2) Load all the data, and handle in the expression using IF statement. This is costly and reduce the performance of Qlikvew.

-Raghu.

QV Data modeling

guya — Wed, 16 Dec 2009 23:02:34 GMT

Could you please provide me with a concret example?

Thx

QV Data modeling

johnw — Thu, 17 Dec 2009 06:38:38 GMT

Method 1:

LOAD *
;
SQL SELECT
A
,B
,C
,Weight
,CASE WHEN Weight <= 1 THEN '0-1kg'
WHEN Weight <= 2 THEN '1-2kg'
WHEN Weight <= 5 THEN '2-5kg'
WHEN Weight <= 10 THEN '5-10kg'
WHEN Weight <= 50 THEN '10-50kg'
ELSE '>50kg' END AS Weight_Category
FROM your database
WHERE your conditions
;

Method 2:

LOAD *
,if(Weight <= 1, '0-1kg'
,if(Weight <= 2, '1-2kg'
,if(Weight <= 5, '2-5kg'
,if(Weight <= 10, '5-10kg'
,if(Weight <= 50,'10-50kg'
, '>50kg'))))) as Weight_Category
;
SQL SELECT
A
,B
,C
,Weight
FROM your database
WHERE your conditions
;

There are other methods as well, but those are probably the easiest to understand. (Edit: In our shop, we can much better afford to waste time on our QlikView server than in our DBMS, so most of this sort of manipulation is done in QlikView (method 2).)

QV Data modeling

Thu, 17 Dec 2009 12:41:36 GMT

guy,

Look at John example. Example is a perfect one.

-Raghu.

QV Data modeling

Thu, 17 Dec 2009 13:42:08 GMT

Perfect!!! Thanks!

QV Data modeling

guya — Thu, 17 Dec 2009 18:54:57 GMT

Brillant ! - it's perfect

Thx you all.

Guy