Crosstabulation problem with milions of rows

[#1]

Hi, I'm trying to make a cross tabulation on a db which is something like:

Store	Client ID
A	1
A	2
A	5
A	6
B	1
B	3
C	2
C	4

what I'm trying to achieve is:

Store	A	B	C
A	100%	25%	25%
B	50%	100%	0%
C	50%	0%	100%

I.e: how many clients of the Store A goes also to Store B or C

I already generated Store2 and ClientID2 in the script, and used an aggr expression to match ClientID with ClientID2 (sum(aggr(if(ClientID2=ClientID,1),ClientID,ClientID2,Store,Store2))

The problem is, with 12 milion rows, qlikview can't elaborate the table, and even limiting the rows at 50.000 doesn't help. Too many iterations I guess. I wasn't even able to check if the expression was correct

Is there any other way to achieve the goal? I thought about using a set analysis (I can obtain the result store by store just using manual filters, but with 198 stores it's quite annoying)

The set analysis should be something like "select all the client IDs with store=current value of the dimension Store, then include all the rows with client ID = Client ID from the precedent statement", but I wasn't able to make it work, althought using the set analysis wizard

any help?

thanks

[Gysbert_Wassenaar]

See attached qvw.

[#3]

Hi, thanks for the replay, it was a big advancement but I need a little bit more help please:)

your solution is:

count({<[Client ID]=p({<[Store]={'A'}>}[Client ID])>}[Client ID])/count([Client ID])

This assume that I have to create an expression for each Store I have (which are many)

Is there a way to subtitute the explicit selection {'A'}? I thought about creating a Store2 frome Store, use it as a dimension in the cross table and replace the above selection with [Store]={$(=Store2)} but it didn't work...

thanks again

[Gysbert_Wassenaar]

Is there a way to subtitute the explicit selection {'A'}?

It's possible to create a single big expression using a pick-match expression in combination with a calculated dimension. But I'm not sure this cure is not worse than the disease. See attached updated example.

[#5]

I see, guess it can't be helped if I can't use a field instead of an explicit value, unless transforming it in a text like in your example (which goes out of memory even with the simple example so I don't even mind to try it on the true dataset)

Thanks anyway!