topic Re: How to group data in a column to create count graph in QlikView

How to group data in a column to create count graph

Tue, 15 Jan 2013 14:01:41 GMT

Hello,

I would like to be able to group data in a column to create a graph.

The column in the table look like this:

DataTypes

red

blue

green

short

long

So I would like to create one group for colors (Colors) and one group for length (Length), Then I want to count the total value of data in each of the groups, and make a percent pie-chart with the two groups.

How can I do this?

Re: How to group data in a column to create count graph

swuehl — Tue, 15 Jan 2013 14:10:14 GMT

You can create an additional field in the script (or a calculated dimension) to classify your records into these two groups:

LOAD

DataTypes,

if(match(DataTypes,'red','green','blue'), 'Colors', 'Length') as DataClasses,

...

FROM ...;

Then use DataClasses as dimensions in your chart and something like

=sum(Value)

as expression. Use 'relative' option to display percentages.

Hope this helps,

Stefan

Re: How to group data in a column to create count graph

Wed, 16 Jan 2013 12:24:34 GMT

Thanks for the reply, but I cannot get the syntax to work. It appears something is wrong with the 'if...' line (Load won't execute).

Any suggestions?

Re: How to group data in a column to create count graph

swuehl — Wed, 16 Jan 2013 12:31:36 GMT

Seems to work for me, just tested this snippet:

LOAD *,

if(match(DataTypes,'red','green','blue'), 'Colors', 'Length') as DataClasses

INLINE [

DataTypes

red

blue

green

short

long

];

Re: How to group data in a column to create count graph

Wed, 16 Jan 2013 13:34:11 GMT

I do not fully understand the syntax you are using.

My goal is to group all colors together (red, blue, green) is a group and all lengths (short, long) in another group.

Does this code do that, and do you put it in the script?

Re: How to group data in a column to create count graph

sujeetsingh — Wed, 16 Jan 2013 13:42:07 GMT

Just try it to do at Load time as

Load

Datatypes

,if(Datatypes="red' or Datatypes='Green' or Datatypes='Blue','Color_Group',

(if(Datatypes='short or Datatypes='lomg','Length_Group'))

Try like this....may be help you...

Re: How to group data in a column to create count graph

Wed, 16 Jan 2013 14:51:15 GMT

Where do I put this? in the script?

I have tried this, and it runs without error, but it just gives me a dimension that har no valid data in it..

Re: How to group data in a column to create count graph

swuehl — Wed, 16 Jan 2013 15:26:49 GMT

You should use the LOAD in your script. If you run my sample code, you should get a new field DataClasses with two values, Colors and Length, which classify your DataTypes values.

Not sure if this is what you want, so maybe you want to elaborate a bit more about your data and the report you need (e.g. by posting a bit more complete table, I assume you've got more than one field to consider here?).

Re: How to group data in a column to create count graph

Thu, 17 Jan 2013 07:22:37 GMT

Ok, I will try to explain again.

I am loading data from a Excel Spreadsheet. The relevant coulmns are as follows:

Ordtime	Lønnsart
1	Arbeidstimer
2	Arbeidstimer
5	Overtid 50%
4	Overtid 50%
8	Sykemleding
10	Overtid 100%
13	Ferie
4	Ferie
8	Egenmelding
8	Sykt barn

I want to group data based on Column 'Lønnsart' the following way:

'Arbeidstimer', 'Overtid 50%' and 'Overtid 100%' into group 'Produksjon'

'Sykemelding', 'Egenmelding' and 'Sykt barn' into group 'Fravær'

Then I want to make a pie chart which sums the 'Ordtime' of the respective groups

(Produksjon=22, Fravær=24)

So I figure the Expression should be 'Sum(Ordtime)'

But I am not able to get the Dimension right..

Re: How to group data in a column to create count graph

swuehl — Thu, 17 Jan 2013 08:58:27 GMT

Ok, same procedure as mentioned above, you can classify your data using a new field created with a match condition:

INPUT:

LOAD *,

if(match(Lønnsart,'Arbeidstimer','Overtid 50%','Overtid 100%'),'Produksjon',

if(match(Lønnsart,'Sykemleding','Egenmelding','Sykt barn'),'Fravær')) as ClassInline

INLINE [

Ordtime, Lønnsart

1, Arbeidstimer

2, Arbeidstimer

5, Overtid 50%

4, Overtid 50%

8, Sykemleding

10, Overtid 100%

13, Ferie

4, Ferie

8, Egenmelding

8, Sykt barn

];

or create a new INLINE table that links to your Lonnsart:

ClassTable:

LOAD * INLINE [

Lønnsart, ClassLinkTable

Arbeidstimer, Produksjon

Overtid 50%, Produksjon

Overtid 100%, Produksjon

Sykemleding,Fravær

Egenmelding,Fravær

Sykt barn,Fravær

];

Use your Class.. field as dimension and sum(Ordtime) as expression. Check attached sample.

Re: How to group data in a column to create count graph

moda1023 — Sat, 08 Sep 2018 19:59:03 GMT

Thanks a lot! Was exactly what i needed to solve my problem

Re: How to group data in a column to create count graph

mahichowdary — Sun, 09 Sep 2018 06:09:06 GMT

Hi Stefan,

This file is not able open because i don't have licence version in my machine.

can you please provide access to this file.

Regards,

Mahi