topic Re: Grouping multiple Expressions under one dimension in Pivot table in QlikView

Grouping multiple Expressions under one dimension in Pivot table

Mon, 04 Aug 2014 14:26:21 GMT

Hi Everyone,

I have a table which has 2 columns Name and Amount.

Name	Amount
AAA	10
BBB	20
AAA	30
CCC	40
DDD	50
EEE	60
BBB	70

I have taken an inline table :

LOAD * INLINE [

KPI

EXTERNAL

INTERNAL

];

There is no relation between 1st table & Inline Table.

Desired Output Pivot Table:

	EXTERNAL			INTERNAL
	Experssion1	Experssion2	Expression3	Expression4	Expression5
AAA	XXXXX	XXXXX	XXXXX	XXXXX	XXXXX
BBB	XXXXX	XXXXX	XXXXX	XXXXX	XXXXX
CCC	XXXXX	XXXXX	XXXXX	XXXXX	XXXXX
DDD	XXXXX	XXXXX	XXXXX	XXXXX	XXXXX
EEE	XXXXX	XXXXX	XXXXX	XXXXX	XXXXX

Re: Grouping multiple Expressions under one dimension in Pivot table

Tue, 05 Aug 2014 06:16:36 GMT

Hi Guys,

Any solution for above issue?

Regards

Roopesh

Re: Grouping multiple Expressions under one dimension in Pivot table

senarath — Wed, 06 Aug 2014 09:25:01 GMT

Hi,

You can use KPI as dimension. After that using slice and dice feature in pivot tables, you can drag dimension to header level.

You can use Valuelist() function by creating synthetic dimensions. but in your case, you could handle with first point.

Synthetic Dimensions - ValueList() (Example)

thanx

Re: Grouping multiple Expressions under one dimension in Pivot table

Wed, 06 Aug 2014 15:26:39 GMT

Hi Senarath,

Thanks for providing the details.

How do i group EXPRESSION 1,EXPRESSION 2 & EXPRESSION 3 under "EXTERNAL" KPI and rest under "INTERNAL" KPI.

Regards

Roopesh

Re: Grouping multiple Expressions under one dimension in Pivot table

Mon, 11 Aug 2014 07:43:55 GMT

Hi Guys,

Is the above issue possible in Qlikview?

Regards

Roopesh

Re: Re: Grouping multiple Expressions under one dimension in Pivot table

Mon, 11 Aug 2014 12:56:24 GMT

Hello,

I was not able to make all the expression visible at once. But please find the attachment as a workaround.

I added a dropdown select for the dimension KPI

and also used Conditional Expression to show and hide the expressions.

I know this is not what is expected, but still thought might be helpful to you. And do not forget to always select atleast one value for KPI field.

Another way is to link these KPI in the data model itself.

Create a KPI_Flag in the script and associate data fields around it.

Thanks,

Singh

Re: Re: Grouping multiple Expressions under one dimension in Pivot table

Mon, 11 Aug 2014 13:03:14 GMT

Hi Angad,

Thanks for showing interest in my issue.

I need to show both INTERNAL & EXTERNAL at a time without selection.

Desired Output Pivot Table:

	EXTERNAL			INTERNAL
	Experssion1	Experssion2	Expression3	Expression4	Expression5
AAA	XXXXX	XXXXX	XXXXX	XXXXX	XXXXX
BBB	XXXXX	XXXXX	XXXXX	XXXXX	XXXXX
CCC	XXXXX	XXXXX	XXXXX	XXXXX	XXXXX
DDD	XXXXX	XXXXX	XXXXX	XXXXX	XXXXX
EEE	XXXXX	XXXXX	XXXXX	XXXXX	XXXXX

Regards

Roopesh

Re: Grouping multiple Expressions under one dimension in Pivot table

Tue, 12 Aug 2014 08:52:56 GMT

Hello,

I do not think this will be feasible since KPI column is unrelated to rest of the data set.

What you can do instead is to remove the KPI from dimension and suffix the expression name with the KPI.

Like:

'Expression4-INTERNAL',

'Expression1-EXTERNAL'

Thanks,

Singh