topic Re: Use previous value if no datapoint on given date in QlikView

Use previous value if no datapoint on given date

Sat, 08 Oct 2011 16:04:33 GMT

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

jonathandienst — Sat, 08 Oct 2011 19:07:41 GMT

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

Sun, 09 Oct 2011 15:10:43 GMT

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.csv	file2.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

file1.csv

file2.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

Thu, 21 Jun 2012 13:30:39 GMT

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

jonathandienst — Fri, 22 Jun 2012 10:35:40 GMT

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