QV Data modeling

[guya]

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

[#2]

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.

[guya]

Hello Raghu,

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

Thx

Guy

[#4]

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.

[guya]

Could you please provide me with a concret example?

Thx

[johnw · Accepted solution]

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).)

[#7]

guy,

Look at John example. Example is a perfect one.

-Raghu.

[#8]

Perfect!!! Thanks!

[guya]

Brillant ! - it's perfect

Thx you all.

Guy