Pivot table & set analysis

[#1]

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

JJ

[swuehl]

Jean-Jacques,

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

Regards,

Stefan

[#3]

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

JJ

[swuehl]

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

[#5]

Thanks Stefan, I will try your solution.

Regards JJ