3 Replies Latest reply: Jun 25, 2012 1:18 PM by Stefan Wühl RSS

    Working with stock figures in QlikView (as opposed to flows)

      Hello,

       

      I have got a pretty general question. We have been working with QlikView for a few months now and while we think it's a good tool for analyzing flow data (additive date) we are wondering how to best work with "stock figures" (amounts that are not additive and cannot simply be aggregated).

       

      On the one hand for flows I have as example "sales". If I have sales on June 3rd and June 15th and June 20th and then I want do to analyze this, it's very easy. If I want to see the flows for June 3rd I get the flows for June 3rd, if i want to see the flows for June as total I just need some month dimension in the data and again it's very easy. Just using the sum() formula will automatically aggregate the data as required.

       

      Now on the other hand suppose I have accounts that have balances for different days. Let's say on June 3rd I have 100 on my bank account, on June 15th I have 200 and on June 20th I have 150 on my bank account.

      In the base data we have stored these balances with balance dates and validity dates.

      So eg the balance of 100 on June 3rd has a balance date of June 3rd and a validity date of June 15th (because the next valid balance has been entered for June 15th).

       

      Now in other reporting tools it's still rather easy to get the balance that is valid on June 10th. For example in a SQL query I say "where reportingdate between balancedate and validitydate" and voila I get the correct balance of 100 that has been entered on June 3rd but is still valid on June 10th.

       

      Now of course I could also make some SQL query in QlikView and only get me the balances for June 10th, however usually I want to analyze over time, therefore I need more data than that, the whole month of June, probably even the whole year or even more.

      And if I want to see the value for a month I cannot simply aggregate it (like for the sales flow data) but I need to do something like display the balance that is valid on the last day of the month (if I decide to analyze on a month level).

       

      How to do this in QlikView? The approach we had is this:

       

      Let's say I have a "time" table (including each day of June, June 1st, June 2nd..... June 30th). Then we have the 3 balances. But because QlikView can only do 1:1 joins we need to replicate the balances (using autogenerate or something) so that  we not only have a record on June 3rd for the June 3rd balance but also on June 4th, June 5th.... up to June 15th

       

      For a very small number of accounts this works fine but once we have a higher amount of accounts and want to analyze a longer period of time this leads us to the issue we are facing:

       

      Say I have 5.000 accounts and I have on average 10 balances per year.

      Say I want to analyze a timeframe of 3 years.

      The base date then is around 150.000 records, which is still an amount that is rather easily manageable.

       

      But using the approach decribed above I now need to replicate the balances so that I not only have those validfrom/validto records but so that I have a balance for each day within the timeframe I want to analyze.

      For 5.000 accounts over 3 years this now results in an amount of about 5.5 million records!

      In some situations we need to analyze around 20.000 accounts even, then we have over 20 million records. Add that we need to do some additional transformations and also additional tables facing similar issues we are very soon entering double digit amounts of GB of RAM required (and more importantly QlikView letting me wait several seconds after each selection change to reclaculate the table/diagram).

       

      So my question is how do other people do it? Are there smarter ways of working with non-additive figures than replicating the data for each day?

      Or don't you use QlikView at all for analysis of non-additive data?

       

      Maybe someone has some examples or can point me in the direction where to find good examples of working with non additive data?

       

      Thanks for any feedback and input on this!

        • Re: Working with stock figures in QlikView (as opposed to flows)
          Stefan Wühl

          If you have a table with numericals (like your calendar date table) and you want to retrieve a value from a table with only intervals (like your balances with given valid from valid to dates / numericals), you can use the QV INTERVALMATCH LOAD  prefix to create a link from each single date to the corresponding interval.

           

          Please check the HELP and this forum for more details and samples  (also the extended version of INTERVALMATCH which allows to use additional key fields, like Account Name).

           

          Hope this helps,

          Stefan

            • Re: Working with stock figures in QlikView (as opposed to flows)

              Thanks for the feedback, we will look into that!

               

              Will that only make the loading faster or also reduce the amount of data QV has to keep in memory (and thus increase performance)?

                • Re: Working with stock figures in QlikView (as opposed to flows)
                  Stefan Wühl

                  As you will see, QV will create a synthetic table to link your dates to the balance intervals. I think it could reduce the amount of memory needed compared to replicating complete balance records for every date, though you probably can do something very similar manually.

                  You can also try using a while loop to create all intermittent records in your intervals.

                   

                  Replicating the records / using Intervalmatch should perform much better then doing an "interval match in the frontend", which is also possible but pretty memory and CPU consuming (using an if() statement to return records that are between valid from and valid to dates).

                   

                  Just give it a try and play around with some different approaches (INTERVALMATCH, replicating records, if() statement in the frontend).

                   

                  If you search the forum a bit, there are lots of samples for each distinct approach, e.g.

                  http://community.qlik.com/message/166190