4 Replies Latest reply: Jun 22, 2012 6:35 AM by Jonathan Dienst RSS

    Use previous value if no datapoint on given date

      Hello,

       

      I would like to see previous value, when there is no data on given date. In other words, I would like QV to assume that value has not changed since last event.

       

      In my example is based on "account balance". Let say we had queried the balance once in a while and we can safely assume there has been no change since last query. I would like to see on the charts the current balance for given date, even if there is no explicit value at this point of time.

       

      In attached QVW try to select dates 2010-04-01 to 2011-04-01 to see what I mean. (I also attached csv file if you wish to playaround at data level.)

       

      Do you have any ideas how to do that?

       

      --

      Thx in advance,

      Bart

        • Use previous value if no datapoint on given date
          Jonathan Dienst

          Bart


          Assuming that you are loading from a csv file - use the file wizard to construct the load statement, and click on "Enable Transformation Step", then select fill an click on the Fill button, select the column to fill, and use the fill condition "is empty", select the "Above" fill type and click OK.

           

          That adds a filters term to the file type definition which will fill the empty spaces in the selected column with the last non-empty value above.

           

          Hope that helps

          Jonathan

            • Re: Use previous value if no datapoint on given date

              Jonathan,

               

              Thanks for quick reply.

              This feature is really nice, but it assumes that I have a row (at given date) for both accounts, but second account is just missing balance value (is null).

               

              Let me try and explain the scenario a bit differently.

              We have the following tables :

               

              file1.csvfile2.csv

              account;date;balance

              acc1;2010-04-01;100401

              acc1;2010-05-02;100502

              acc1;2010-10-03;101003

              acc1;2010-10-04;101004

              acc1;2010-10-05;101005

              acc1;2011-04-01;110401

              acc1;2011-05-02;110502

              acc1;2011-10-03;111003

              acc1;2011-10-04;111004

              acc1;2011-10-10;111010

              account;date;balance

              acc2;2010-04-01;200401

              acc2;2010-05-02;200502

              acc2;2011-10-05;211005

               

              As you can see, some datapoint do not align (ie. last rows). That means that when I ask for data 2011-10-05, then QV will show me only acc2 (no data for acc1 on 2011-10-05). I would like QV to travel back and find that last value for acc1 was on 2011-10-04 and display it (of course it would be best if I could tell this is taken from previous date, but let's focus on getting in working first ). Final effect: 2011-10-05 shows acc1.balance=111004 and acc1.balance=211005.

               

               

              [yet another way of defining the question] It is the same as displaying data from measurements. One event is polled with hourly frequency, other is polled every second hour, but I always want to see both values, no matter which hour I ask for.

               

              Is the possible?

              Or maybe such thing should be approach differently?

               

              BR,

              --

              Bart

                • Re: Use previous value if no datapoint on given date

                  Hello Bart,

                  did you solve your issue? If yes please can you share with me on how you solved it?

                  I am facing a similar issue on financial data on projects. Projects are having values monthly: jan,feb, marc,....dec.

                  But not necessarily for all months, some projects has values only for some months(The values are actual values)

                  In fact i need the actual value on quarters (ie march, june, sep and dec) for each project. For projects that have values for all the months, its easy, i just pick value on march, june, sep and dec).

                  My problem is for project that do not have values for all months. Ex: a project have actuals only for jan and feb, so for this case my quarterly values on march, june, sep adn dec will be my february value because it is the last value i have for this project !!

                   

                  I hope you understand my issue.

                   

                  thank you in advance  for your help

                  • Re: Use previous value if no datapoint on given date
                    Jonathan Dienst

                    Bart

                     

                    It can be done, but its a bit more complex. The steps would be as follows:

                     

                    • Load file1 normally into table tmpData, saving balance as tmpbalance1.

                     

                    tmpData:

                    LOAD account,

                         data,

                         balance as tmpbalance1

                    FROM file1.csv (...)

                    ;

                     

                    • (Outer) Join file2 to tmpData on account and date, and balance as tmpbalance2

                     

                    JOIN (tmpData)

                    LOAD account,

                         date,

                         balance as tmpbalance2

                    FROM file2.csv (...)

                    ;

                     

                    • Now you have dates and accounts, tmpbalance1 (which contains values that exist in file, null where none) and tmpbalance2 (values that exist in file2, null where none). Finally load the data from tmpData into Data and drop tmpData, like this:

                    Data:

                    LOAD account,

                         date,

                         If(IsNull(tmpbalance1), If(account = Previous(account), Previous(tmpbalance1)), tmpbalance1) As balance1,

                         If(IsNull(tmpbalance2), If(account = Previous(account), Previous(tmpbalance2)), tmpbalance2) As balance2

                    Resident tmpData

                    Order by account, date;

                     

                    Drop Table tmpData;

                    Note that this will return null for any missing values before the first loaded value for each account.

                     

                    Regards

                    Jonathan