4 Replies Latest reply: Oct 6, 2014 4:17 PM by Alessandro Ferrarese

# Crosstabulation problem with milions of rows

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

StoreClient ID
A1
A2
A5
A6
B1
B

3

C2
C4

what I'm trying to achieve is:

StoreABC
A100%25%25%
B50%100%0%
C50%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

• ###### Re: Crosstabulation problem with milions of rows

See attached qvw.

• ###### Re: Crosstabulation problem with milions of rows

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

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

• ###### Re: Re: Crosstabulation problem with milions of rows
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.

• ###### Re: Re: Crosstabulation problem with milions of rows

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!