Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
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
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?
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
];
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?
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...
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..
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?).
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..
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.