Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.


View solution in original post

11 Replies
swuehl
MVP
MVP

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
Author

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?

swuehl
MVP
MVP

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
Author

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
Master III
Master III

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
Author

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

swuehl
MVP
MVP

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
Author

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

swuehl
MVP
MVP

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.