topic Re: Help please PIVOT / AGGR / SET Analysis in QlikView

Help please PIVOT / AGGR / SET Analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I need a help here. I have a dataset as shown below. The data is coming from 2 diffrent source SYS1 and SYS2. The Column SOURCE tells where the record is coming from. and now i have to render this data in pivot table with extra colum as shown below (diagram2)

I.e what i want is if you see Column Output in diagram 2. It should show the from where the data is come from

Example:

A E I S are presenet in both system so Output is SYS1 and SYS2

B G O U olny in SYS2 So output in SYS2

Would you please paste expression here as im not using license version.

Source	Col1	Col2	Col3	Col4
Sys1	A	E	I	S
Sys2	A	E	I	S
Sys2	A	E	M	T
Sys1	A	F	N	U
Sys2	B	G	O	P
Sys1	C	H	P	W
Sys2	C	H	P	W
Sys1	C	H	P	X
Sys2	C	H	P	X
Sys2	D	I	Q	Y
Sys1	D	J	R	Z
Sys1	D	K	R	AA
Sys2	D	K	R	AA

Level1	Level2	Lev2l3	Level4	Output
A	E	l	S	Sys1, Sys2
	E	M	T	Sys1
	F	N	U	Sys2
B	G	O	V	Sys1
C	H	P	W	Sys1,Sys2
C	H	P	X	Sys1,Sys2
D	I	Q	Y	Sys2
D	J	R	Z	Sys1
D	K	R	AA	Sys1,Sys2

Re: Help please PIVOT / AGGR / SET Analysis

ChennaiahNallani — Thu, 16 Aug 2018 06:13:38 GMT

try like below

exp:

Concat( Distinct Source, ',')

Re: Help please PIVOT / AGGR / SET Analysis

Sergey_Shuklin — Thu, 16 Aug 2018 06:14:10 GMT

Hello!

Create pivot table with four dimensions and one expression with concat() function.

Smth like that:

=Concat(distinct Source, ',')

Re: Help please PIVOT / AGGR / SET Analysis

Anonymous — Thu, 16 Aug 2018 06:20:46 GMT

Thanks Sergey. Ya it helped.

If i want to show the output value in Mouse hover in Col4

Re: Help please PIVOT / AGGR / SET Analysis

ChennaiahNallani — Thu, 16 Aug 2018 06:25:25 GMT

Re: Help please PIVOT / AGGR / SET Analysis

Sergey_Shuklin — Thu, 16 Aug 2018 06:42:59 GMT

Col4? Where you have S, T, U values?

If so, you can use calculated dimension Col4:

=Col4

&chr(10)&

aggr(

Concat(distinct Source, ',')

,Col1

,Col2

,Col3

)