topic Re: Pie chart - categorize in QlikView

Pie chart - categorize

Fri, 10 Jun 2016 19:51:59 GMT

Hi All,

I need to create some pie charts based on a simple table, but can't figure out how to do it. I've attached the table example, and here's what I need to do:

Each person owns buckets of fruits(apple,orange,kiwi,blueberry), vegetables(broccoli,mushroom,tomato), or burgers(cheeseburger, hamburger). I need to create 3 pie charts - one for each category (fruits, vegetables, burgers), and need to show how many 1s or 2s or 3s or 4s or 5s or blanks I have for each category. So the pie charts will have total counts for '1','2','3','4','5',blanks.

Example for fruits pie chart: It has to show the following numbers: '1': 3, '2':4, '3':5, '4':4, '5': 11, blanks: 13

Same pattern for the other two charts.

I have no idea how to start and how to approach this? Should I categorize separately in the load scripts, or is there a way to do it in the pie chart?

Re: Pie chart - categorize

sunny_talwar — Fri, 10 Jun 2016 20:10:51 GMT

May be change the data model to this:

CrossTable (Product, Value)

LOAD * INLINE [

ID, apple, orange, kiwi, blueberry, mushroom, tomato, broccoli, cheeseburger, hamburger

329, 5, , 2, 5, 3, 5, 2, , 1

235, , , 4, , 3, , , 3

455, 2, , 5, 3, 3, 2, 4, 4

355, 4, 5, 3, 5, , 5, 1, 2

346, , 3, , 5, 5, , 1, , 4

728, 5, , 5, 5, , 3, 2

320, 1, 4, 1, , 2, 2, 3, 5

956, , , 1, 3, 4, 4, 4

557, 4, 3, , 2, 5, 1, , , 3

235, 5, 2, , 5, 3, 1, 1, , 2

];

LOAD * Inline [

Product, Category

apple, Fruit

orange, Fruit

kiwi, Fruit

blueberry, Fruit

mushroom, Vegetables

tomato, Vegetables

broccoli, Vegetables

cheeseburger, Burgers

hamburger, Burgers

];

Re: Pie chart - categorize

Fri, 10 Jun 2016 20:19:07 GMT

Thanks Sunny, this works. only problem is I had my file as an example, however, I have a big table with so many other columns too (other than the fruits, etc.) how can I do it still? So how can I have all my table loaded once more in the load script? Imagine we call the main table MainTable

Re: Pie chart - categorize

sunny_talwar — Fri, 10 Jun 2016 20:25:45 GMT

Difficult to suggest without seeing a sample? Can you share the list of columns with may be just one row of data in it to see how it may look?

Re: Pie chart - categorize

Fri, 10 Jun 2016 20:36:02 GMT

yes so data is actually coming from another file (excel sheet), but since I cant share the whole thing, I'll give you an example of a row:

city

country

Re: Pie chart - categorize

sunny_talwar — Fri, 10 Jun 2016 20:54:19 GMT

You can try like this:

CrossTable (Product, Value, 5)

LOAD ID,

city,

country,

category,

gender,

apple,

orange,

kiwi,

blueberry,

mushroom,

tomato,

broccoli,

cheeseburger,

hamburger

FROM Source.....

5 in red is the number of columns which you don't want to transform into rows. Look here for more details on CrossTable LOAD: The Crosstable Load

Re: Pie chart - categorize

Sun, 12 Jun 2016 18:58:54 GMT

Can I do load resident? instead of load from? If so, how can I do it? right now when I do load resident, it gives me garbage after error.

Re: Pie chart - categorize

sunny_talwar — Sun, 12 Jun 2016 20:50:09 GMT

You can do Resident load, can you share how your script looks like?

Re: Pie chart - categorize

Sun, 12 Jun 2016 21:31:13 GMT

I first have some script that builds up my MainTable. Then after all those (and I have only one table), I want to do what I've explained in this thread and you're helping me with.

so now this is what I have, and I get "garbage after statement LOAD * Inline..." error

CrossTable (Product, Value, 5)

LOAD ID,

city,

country,

category,

gender,

apple,

orange,

kiwi,

blueberry,

mushroom,

tomato,

broccoli,

cheeseburger,

hamburger

Resident MainTable;

LOAD * Inline [

Product, Category

apple, Fruit

orange, Fruit

kiwi, Fruit

blueberry, Fruit

mushroom, Vegetables

tomato, Vegetables

broccoli, Vegetables

cheeseburger, Burgers

hamburger, Burgers

];

Re: Pie chart - categorize

Mon, 13 Jun 2016 01:55:32 GMT

Also, you know what I realized, I don't want to have several rows for each ID in my records. Using cross table will result in that. So is there a way to just categorize those on the front end to plot the pie charts (maybe using if?)?