topic Re: Scripting Issue - please help in QlikView

Scripting Issue - please help

samvile18 — Tue, 06 Sep 2011 10:41:57 GMT

Hello fellow Qlikviewans,

I have the following syntax which takes data from my FUM QVD every month. Now instead of the new data overwriting what I already have in the below table, I want the new data to be appended to the bottom instead...can someone PLEASE let me know how to script this as I'm pulling my hair out?!!

FUM:

LOAD [Bid Price],

DOB,

[Fund Name],

[Fund No],

[Fund Value],

[Policy Number],

[Scheme Name],

[Scheme Number],

Sex,

Units,

[Policy Status],

[Valuation Date]

FROM

(qvd)

Scripting Issue - please help

IAMDV — Tue, 06 Sep 2011 14:18:20 GMT

Hi Mate,

Let me make sure if I have understood the requirement. You wanted to load only new data from QVD instead of all the historical data... is this right? If yes, I would recommend using functions like RowNo() and RecNo() inyour QVD Load Script. Then you can store the most recent RowNo() or RecNo() ina script variable. Once you have this variable in your load script you can do the conditional load by using the “Where” clause. This way you are always loading the most recent data.

I hope this helps!

Good luck!

Cheers - DV

Scripting Issue - please help

samvile18 — Tue, 06 Sep 2011 14:31:08 GMT

Hello mate,

Thats not quite right due to the way the data is set up, let me explain a bit further:

I have a DB which holds the current months FUM (Funds Under Management) data (about 500,000 records each month). The DB doesn't hold historical data as there's too much for Access to handle, therefore all I'll ever be loading to the QVD is the latest months worth of data.

That means I basically want to take everything from the QVD and append it to the bottom of what I already have in my QVW table, does that make sense. The QVD will hold only new data each time it's refreshed.

Scripting Issue - please help

Tue, 06 Sep 2011 14:47:34 GMT

Your case seems to be rather basic. Take a look to QlikView Reference Manual Section 25.7 Using QVD files for incremental load

It has very well written description of how to arrange incremental load. You will need a timestamp or any other key that would help you to identify rows that needs to be appended.

Scripting Issue - please help

samvile18 — Tue, 06 Sep 2011 14:52:10 GMT

Unfortunately I do not have a Reference manual, I'll look to see if I can download one.

Also, I don't really need to pick certain rows as everything will need to come accross. I just can't figure out how to load it without overwriting the previous data.

Scripting Issue - please help

Tue, 06 Sep 2011 14:55:47 GMT

Reference Manual usually comes with QlikView installation -- look for folder Documentation in QlikView folder (make sure you made complete install).

There're plenty of them in internet as well.

Scripting Issue - please help

Tue, 06 Sep 2011 14:58:19 GMT

Also, I don't really need to pick certain rows as everything will need to come accross. I just can't figure out how to load it without overwriting the previous data.

Usually you need to load previous data from QVD file using LOAD, append new data from database using SQL SELECT and then write all together into the same QVD file using STORE.

Re: Scripting Issue - please help

IAMDV — Tue, 06 Sep 2011 15:00:37 GMT

Mate,

Here are the reference manual(s).

Meanwhile, I will look into my sample repository and I'll keep you posted.

Cheers - DV

Re: Scripting Issue - please help

IAMDV — Tue, 06 Sep 2011 15:09:35 GMT

Also, one more useful document on incremental loading.

Cheers - DV

Scripting Issue - please help

samvile18 — Wed, 07 Sep 2011 14:44:18 GMT

Chaps....thanks for the help and links to documents etc.

I've just finished building the QVD creator and it works an absolute treat, so, thanks once again.