topic Re: Pivot table & set analysis in QlikView

Pivot table & set analysis

Wed, 18 Jan 2012 13:34:18 GMT

Hi,

This my input table

IdLogement	IdModalite	IdIndic
1	2	1
1	3	2
2	5	1
2	1	2
3	3	1
3	1	2
4	4	1
4	3	2
5	4	1
5	3	2
6	4	1
6	2	2
7	3	1
7	3	2
8	3	1
8	1	2
9	4	1
9	2	2

As you can see, for each IdLogement I have 2 rows, one for the IdIndic=1 and one for the IdIndic=2

I'd like to create a pivot table where :

- my first dimension is IdModalite obtain when IdIndic = 1

- my second dimension is IdModalite obtain when IdIndic = 2

- my expression must be the count of distinc IdLogement in each intersection of the 2 dimensions

So the result should be a matric 5 * 3 .

Thanks for any help

Re: Pivot table & set analysis

swuehl — Wed, 18 Jan 2012 15:10:46 GMT

Jean-Jacques,

not sure if this is what you want, I get a 4x3 matrix.

Regards,

Stefan

Re: Pivot table & set analysis

Wed, 18 Jan 2012 15:38:47 GMT

Hi Stefan

Not exactly what I want. Because, I don't want to transform my original table. In the real life, this table is a monster with 200 millions rows so I wonder if it's possible to create 2 dimensions with only one field playing with the value of the field IdIndic. I'm not very comfortable with set analysis directly in Dimension.

Anyway, thanks for your effort.

see u

Re: Pivot table & set analysis

swuehl — Wed, 18 Jan 2012 18:39:20 GMT

Jean-Jacques,

your two dimensions are not independent from another, one is already limiting the scope of the second. So if you just limit the two dimensions by IdIndic, you won't get any matching records for combined dimension values (intersection in the pivot table). You could see this in my first attached sample on the bottom.

I also noticed that my first attempt show wrong values, please disregard this completely.

Maybe you could at least create two distinct lists (call them IDM1 and IDM2) of your IdModalite, filtered by IdIndic. I hope / assume that these lists are not very large (like in your sample, 3 and 4 values).

Then you could use

=count(distinct if((IDM1= IdModalite and IdIndic=1) or (IDM2=IdModalite and IdIndic=2),IdLogement))

to get what you want (at least what I think you want). Probably not very performant on your data set, but I haven't found a way to use e.g. set analysis here.

Regards,

Stefan

Re: Pivot table & set analysis

Thu, 19 Jan 2012 09:16:00 GMT

Thanks Stefan, I will try your solution.

Regards JJ