Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Not applicable

How to group data in a column to create count graph

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
red
blue
blue
green
green
short
short
long
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?

Tags (3)
1 Solution

Accepted Solutions
MVP
MVP

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

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.


11 Replies
MVP
MVP

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

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

Not applicable

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

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?

MVP
MVP

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

Seems to work for me, just tested this snippet:

LOAD *,

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

INLINE [

DataTypes

red

red

blue

blue

green

green

short

short

long

long

];

Not applicable

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

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?

sujeetsingh
Honored Contributor III

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

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

Not applicable

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

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

MVP
MVP

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

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

Not applicable

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

Ok, I will try to explain again.

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

OrdtimeLønnsart
1Arbeidstimer
2Arbeidstimer
5Overtid 50%
4Overtid 50%
8Sykemleding
10Overtid 100%
13Ferie
4Ferie
8Egenmelding
8Sykt 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..

MVP
MVP

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

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.