topic Re: How to Group Values within a Field and show on Bar Chart in QlikView

How to Group Values within a Field and show on Bar Chart

Thu, 14 Mar 2013 17:06:51 GMT

Hi,

I have a chart with two dimensions ("Region", "Case Type") and one expression ("Hours"). My chart currently looks like below:

The numbers on the Y-axis denote the "Hours". X-axis has the "Region" values and different "Case Type"s are stacked for each region (each case type colored differently).

I'd like to know if I can put a number of these "case type"s into different groups. Do you have any suggestions on how to achieve this?

To give an example - Let's say "Case Type" dimension has a total of 5 values: AB, CD, EF, GH, IK. What I'd like to do is group these different fields - like 'AB' and 'CD' become 'Field_1', 'EF' and 'GH' become 'Field_2', and 'IK' stays as 'IK'. In the end, I'd like to show these grouped values ('Field_1', 'Field_2' and 'IK') stacked for each region (3 colors instead of 5 colors for each region).

I can't use grouping, since grouping works for different fields and I'm trying to group different values for a field.

Thanks in advance,

Re: How to Group Values within a Field and show on Bar Chart

Gysbert_Wassenaar — Thu, 14 Mar 2013 17:10:32 GMT

Create either a new field in the script or a calculated dimension in your chart (the former is the better option). Something like:

if(match([Case Type],'AB','CD'),'Field_1',if(match([Case Type],'EF','GH'),'Field_2','IK'))

Re: How to Group Values within a Field and show on Bar Chart

Thu, 14 Mar 2013 17:46:37 GMT

Thanks for your answer Gysbert. I'm using the calculated dimension method however it gives me a syntax error.

I'm using this expression in the "Edit Expression" window:

If(Match([Case Type], 'AB', 'CD'), 'Field_1', If(Match([Case Type], 'EF', 'GH'), 'Field_2', 'IK'))

Am i doing something wrong?

Re: How to Group Values within a Field and show on Bar Chart

MayilVahanan — Thu, 14 Mar 2013 17:53:22 GMT

Expression is correct.

=If(Match([Case Type], 'AB', 'CD'), 'Field_1', If(Match([Case Type], 'EF', 'GH'), 'Field_2', 'IK'))

Can you post a sample file or screenshot ur expression with error msg?

Re: How to Group Values within a Field and show on Bar Chart

Gysbert_Wassenaar — Thu, 14 Mar 2013 18:12:16 GMT

If you get a syntax error the most likely cause is that Case Type is not the name of your dimension. You posted that Case Type was the dimension so that's what I used. Replace Case Type with the exact name of your dimension.

Re: How to Group Values within a Field and show on Bar Chart

Fri, 15 Mar 2013 00:58:20 GMT

no, that's not the problem.

Re: How to Group Values within a Field and show on Bar Chart

Fri, 15 Mar 2013 01:12:36 GMT

Thanks for the answer Mayil. I'm not writing that exact expression (which I gave as an example above). Sorry for not being very clear.

This is the expression that I'm writing in the "Calculated Dimension" - Dimension name and field names are modified (I'm using QlikView 11) :

=If(Match([Case Type], 'AB EXAMPLE', 'EXAMPLE CD'), 'Field 1', If(Match([Case Type], 'EF', 'GH'), 'Field 2',

If(Match([Case Type], 'EF EXAMPLE', 'EXAMPLE GH'), 'Field 3', If(Match([Case Type], 'DD EXAMPLE', 'EXAMPLE EE'), 'Field 4', If(Match([Case Type], 'DC EXAMPLE'), 'Field 5', 'IK EXAMPLE'))

Re: How to Group Values within a Field and show on Bar Chart

Fri, 15 Mar 2013 04:26:23 GMT

Can you upload the sample application

Re: How to Group Values within a Field and show on Bar Chart

Gysbert_Wassenaar — Fri, 15 Mar 2013 09:22:52 GMT

If you nest if statements you need to make sure all the parentheses match:

=If(Match([Case Type], 'AB EXAMPLE', 'EXAMPLE CD'), 'Field 1',

If(Match([Case Type], 'EF', 'GH'), 'Field 2',

If(Match([Case Type], 'EF EXAMPLE', 'EXAMPLE GH'), 'Field 3',

If(Match([Case Type], 'DD EXAMPLE', 'EXAMPLE EE'), 'Field 4',

If(Match([Case Type], 'DC EXAMPLE'), 'Field 5', 'IK EXAMPLE')))))

That said, you should probably create an extra field in the script using a mapping table and applymap. That will perform a lot better than if statements nested four levels deep.

Re: How to Group Values within a Field and show on Bar Chart

Wed, 27 Jan 2016 23:02:18 GMT

Hi Gysbert,

Thanks for the tip. I have one more requirement in the below expression.

=If(Match([Case Type], 'AB EXAMPLE', 'EXAMPLE CD'), 'Field 1',

If(Match([Case Type], 'EF', 'GH'), 'Field 2',

If(Match([Case Type], 'EF EXAMPLE', 'EXAMPLE GH'), 'Field 3',

If(Match([Case Type], 'DD EXAMPLE', 'EXAMPLE EE'), 'Field 4',

If(Match([Case Type], 'DC EXAMPLE'), 'Field 5', 'IK EXAMPLE')))))

For Eg: If Field 2 does not have any value, it is not getting displayed in the chart. I have tried removing the Suppress Null option. But it did not work out.

I want to have Field 2 and it should show as zero. How can I achieve it? Thanks in advance.