topic Re: How can I group data in one dimension? in QlikView

How can I group data in one dimension?

Tue, 01 Apr 2014 12:41:41 GMT

In edit script you can do something like this:

The field "no_of employees" is segregated and a new field "employee_range" is made.

Inside Qlikview you can make a listbox using the "employee_range" dimension.

And my problem is: How can implement this inside Qlikview?

I have a field with the list of companies and total cars per company. I want to create a new dimension to separate these companies based on the number of cars.

My plan is like this:

To make a dimension named total_car_range with with sub fields inside like this:

>= 250 cars

100-249 cars

50-99 cars

20-49 cars

10-19 cars

I hope you understand my point. Thanks.

Re: How can I group data in one dimension?

MK_QSL — Tue, 01 Apr 2014 12:45:57 GMT

Can you provide some sample data?

Re: How can I group data in one dimension?

nizamsha — Tue, 01 Apr 2014 12:47:15 GMT

if(Employees<=5,dual('5',1),

if(Employees>=6 and Employees<=15,dual('0-15',2))

like this u can create it will work

Re: How can I group data in one dimension?

Gysbert_Wassenaar — Tue, 01 Apr 2014 12:48:02 GMT

You can create that field in the script. As long as you don't need to change the ranges on the fly in the front end then creating the field in the script is the best solution. If you must create the range field dynamically in the front end you can use the approach described here: Users can map/group dimensions at run-time!

Re: How can I group data in one dimension?

sujeetsingh — Tue, 01 Apr 2014 12:48:54 GMT

Use this

Tab1:

LOAD * INLINE [

ID, Key, Value

1, a, 1

2, b, 1

3, c, 3

4, d, 4

5, e, 4

];

Data:

ID, Key as KEY, Value

,if(Value>=1 and Value<3,'1-3',if(Value>=3 and Value<5 ,'3-5',if(Value>=5,'+5'))) as Range

resident Tab1;

drop table Tab1;

Re: How can I group data in one dimension?

Tue, 01 Apr 2014 12:56:08 GMT

I can't simply create the field in the edit script. Unlike in the "no_of_employees" field where the data is already fix meaning no more calculations made so its easy to separate them.

The reason I can't create the field is I need to sum up all cars per company first to get the data I want. Once I have the data that's the time I want to segregate it. Unless I can calculate the data in the edit script. Thanks

Re: How can I group data in one dimension?

Gysbert_Wassenaar — Tue, 01 Apr 2014 12:59:00 GMT

Any reason why you can't sum the cars per company in the script?

Re: How can I group data in one dimension?

its_anandrjs — Tue, 01 Apr 2014 13:03:12 GMT

Provide some data or write a code for that field like

if( YourField >= 10 and YourField <= 10,'10-19 cars',

If( YourField >= 20 and YourField <= 49,'20-49 cars',

if( YourField >= 50 and YourField <= 99,'50-99 cars',

if( YourField >= 100 and YourField <= 249,'100-249 cars',

if( YourField >= 250,'250 cars'))))) as total_car_range

Hope this helps

Thanks & Regards

Re: How can I group data in one dimension?

Tue, 01 Apr 2014 13:30:01 GMT

Ok here is my sample data from a database:

flt_company_id:

1 = company1

2 = company2

3 = company3

flt_brand_id:

1 = audi

2 = bmw

3 = mercedez

4 = toyota

5= opel

6 = honda

7 = suzuki

What I want is separate each company by the sum of all their cars.

company1 has 50 cars

company2 has 110 cars

company3 has 75 cars

So how can I do it in edit script? Or is their a way to make it inside Qlikview? Thanks

Re: How can I group data in one dimension?

MarcoWedel — Tue, 01 Apr 2014 14:03:22 GMT