topic Re: Club and Display two dimension values into one in QlikView

Club and Display two dimension values into one

Sun, 14 Dec 2014 21:36:47 GMT

Hi all, I have a table containing multiple columns and I am displaying a graph based on two columns, Attributes and Values. There are five attributes (A , B, C, D and E ) and I am showing a simple bar graph of values for each of them. I have set Attributes as dimension and sum(value) as expression. My requirement is to not display all attributes separately as five bars but instead A and B clubbed into one bar called AB, C will remain as such and finally D and E combined into a bar called DE. Just three bars as final. I tried creating calculated dimensions but I am unable to, Can someone help me here?

Re: Club and Display two dimension values into one

Vegar — Sun, 14 Dec 2014 21:58:13 GMT

A fast solution to your problem is to define one expression per calculation requirement in your chart.

SUM({$<Attribute = {'A','B'}>}Value)
SUM({$<Attribute = {'C'}>}Value)
SUM({$<Attribute = {'D','E'}>}Value)

Vegar Lie Arntsen

Re: Club and Display two dimension values into one

Sun, 14 Dec 2014 22:24:31 GMT

I tried this Let's say I just add the first expression, I still get two bars. I need just one bar having the sum of both..

Re: Club and Display two dimension values into one

jochem_zw — Sun, 14 Dec 2014 22:26:58 GMT

At properties, style, subtype choose stacked bars.

Re: Club and Display two dimension values into one

Vegar — Sun, 14 Dec 2014 22:31:22 GMT

O I forgot to mention that you should remove the Attribute as a dimension. You are "replacing" the dimension with multiple expressions.

Re: Club and Display two dimension values into one

Sun, 14 Dec 2014 22:48:01 GMT

Thanks this works but is there a better solution? I just provided a sample question and in the real application the graph as two more expressions depending on the same dimension.

Is there a way I can create dimensions for clubbing?

Re: Club and Display two dimension values into one

Vegar — Sun, 14 Dec 2014 22:56:15 GMT

I would have created a new dimension in my script where I assign my transactions to my dimensions. There are several ways to do it, but the most fool proof is to use a mapping table.

My_map:
MAPPING LOAD * INLINE [
OrigDim, NewDim
A, AB
B, AB
C, C
D, DE
E, DE
]

Trans:
LOAD
     *,
     ApplyMap('My_map', OrigDim, null() ) as NewDimension
FROM ... ;

//Vegar

Re: Club and Display two dimension values into one

Sun, 14 Dec 2014 23:04:39 GMT

Thank you Vegar, I will explore this solution.