topic Re: Store data as history in QlikView

Store data as history

Thu, 11 Oct 2012 07:04:27 GMT

Hello!

I am quite new to Qlikview but I must say, it is a great tool and easy to learn.

However, my first application is close to release but I have one last function that I need to get working.

My application read some values from a .qvd. Some of those field I would like to store on daily bacis.

And in my application the user will be able to select different dates and get the values from that selected year/month/day.

My store function works fine, I guess. But the problem is that I keep overwriting my data everytime, which is quite obvious when you see my code.


SaveHistory:
load value1,
value2,
Today() as vDate,
Year(Today()) as vYear,
Month(Today()) as vMonth,
Week(Today()) as vWeek,
Day(Today()) as vDay,
Resident MyDataSourceTable;

store SaveHistory into C:\Test\Qlikview\Project\New folder\test.qvd; // (qvd)
drop table SaveHistory;

 
 //Load history to use in graphs etc.
History:
Load value1,
value2,
vYear,
vMonth,
vWeek,
vDay,
vMinute
//vDate2
From C:\Test\Qlikview\Project\New folder\test.QVD (qvd);

How do I update my history with the new value? Ex. 2012-12-24 value1 as "christmas" and 2012-12-25 value1 as "hangover"

Re: Store data as history

Thu, 11 Oct 2012 07:21:43 GMT

He Jesper,

If I understand your problem correctly you are trying to add new data to a qvd file?

And then save that new file.

You can add data by using the concetenate load function.

For eg

History:

Load *

from

C:\Test\Qlikview\Project\New folder\test.qvd;

concetenate load

value1, value2, Today() as vDate, Year(Today()) as vYear, Month(Today()) as vMonth, Week(Today()) as vWeek, Day(Today()) as vDay, Resident MyDataSourceTable;

It's best practice to write variables as vDate,vYear,vMonth etc. Right now you are using the naming of variables for fields, which might be confusing for someone else seeing your code.

Hope this helps.

gr.

Frank

Re: Store data as history

Thu, 11 Oct 2012 08:10:02 GMT

Thank you Frank! I'm slowly getting there!

However I seem to write over the previous data when I store the field values?

I want the new qvd to contain data from today - linked to that specific date. And all field values from yesterday, linked to 2012-10-10.

SaveHistory:
Load *
from
C:\Test\Qlikview\Project\New folder\test.qvd;
Concatenate load
values1,
values2,
Today() as vDate,
//'2012-10-10' as vDate,
Year(Today()) as vYear,
Month(Today()) as vMonth,
Week(Today()) as vWeek,
Day(Today()) as vDay
Resident Load1;

store SaveHistory into C:\Arbete\Qlikview\Project\New folder\test.qvd; // (qvd)
drop table SaveHistory;
//Loading
History:
Load values1 as something1,
values2 as something2,
vDate,
vYear,
vMonth,
vWeek,
vDay
From C:\Test\Qlikview\Project\New folder\test.QVD (qvd);

if I try manually put in another date (you might see that comment with yesterdays date), when I then compile todays date will disappear from the field vDate?

I believe that vDate should contain both 2012-10-11 and 2012-10-10 by now, but it doesn't.

Thanks for your time!

Re: Store data as history

Thu, 11 Oct 2012 08:59:32 GMT

You are using two different files:

C:\Arbete\Qlikview\Project\New folder\test.qvd;

C:\Test\Qlikview\Project\New folder\test.QVD

Maybe that's the reason you don't see the today and yesterday.

Re: Store data as history

Thu, 11 Oct 2012 09:10:12 GMT

Oh sorry for the confusion, but that is not the problem. I forgot to change that when I pasted the code...

I will try to explain my self better:

Every day, the fields will be stored in the qvd - sorted under the specific date.

* 2012-10-10, values1 might contain 123123 and 5125412.

* 2012-10-11, values1 will contain 246561 and 251145.

In my application, the user should be able to choose between dates and get the values from that specific day.

Hope that clarify some

Thanks again!!

Re: Store data as history

Thu, 11 Oct 2012 09:48:13 GMT

Hey Jesper,

Concatenate load should do the trick here. So probably something else is going wrong.

Try making it as simple as possible. For eg you don't need to save vYear, vMonth,vWeek andvDay.

Saving vDate should be enough,vYear,vMonth,vWeek,vDay can be derived from vDate.

Your data should like this:

Historical:

vDate Value1 Value2

2012-10-10 123123 456

2012-10-10 5125412 457

New data:

vDate Value1 Value2

2012-10-11 246561 460

2012-10-11 251145 461

So concatenating the new data to Historical should work.

Could you look in the table viewer (ctr-t) and check how the table History looks when loaded?

Re: Store data as history

Thu, 11 Oct 2012 10:45:34 GMT

I found the problem. I had forgotten the (qvd) reference in

SaveHistory: Load * from C:\Test\Qlikview\Project\New folder\test.qvd;

C:\Test\Qlikview\Project\New folder\test.qvd (qvd); solved the issue

Your first answer was the solution, thanks again Frank!