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
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 :
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?
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
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.
balance as tmpbalance1
FROM file1.csv (...)
(Outer) Join file2 to tmpData on account and date, and balance as tmpbalance2
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:
If(IsNull(tmpbalance1), If(account = Previous(account), Previous(tmpbalance1)), tmpbalance1) As balance1,
If(IsNull(tmpbalance2), If(account = Previous(account), Previous(tmpbalance2)), tmpbalance2) As balance2
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.