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

    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