topic Re: Qvd file not able create in incremental load in QlikView

Qvd file not able create in incremental load

Anonymous — Wed, 13 Jan 2016 15:38:57 GMT

Hi,

I am facing one issue. I have fact table file data and it is overriding daily. If I do normal script, QVD file will be override when fact table data is getting override on daily basis. So to overcome that, I have written code like this

If FileSize('$(Vqvdpath)test.qvd') > 0 then

test1:

LOAD * FROM fact table;

Store table into test.qvd(qvd);

DROP Table test1;

ENDIF.

But it is not creating any QVD file. please help me where it is going wrong.

Regards,

Kumar Reddy

Re: Qvd file not able create in incremental load

sunny_talwar — Wed, 13 Jan 2016 15:42:17 GMT

Do you need the Vqvdpath variable in STORE statement as well? and also you need to store test1 and not Table.

If FileSize('$(Vqvdpath)test.qvd') > 0 then

test1:

LOAD * FROM fact table;

STORE test1 into $(Vqvdpath)test.qvd(qvd);

DROP Table test1;

ENDIF

Re: Qvd file not able create in incremental load

Anonymous — Wed, 13 Jan 2016 15:53:28 GMT

When I do incremental loads I want the original qvd to be overwritten with the new version I have added the incremental rows to.

Except for some scenarios when I want a new qvd for each day's data. Is this what you wish to do ?

Re: Qvd file not able create in incremental load

sunny_talwar — Wed, 13 Jan 2016 15:54:58 GMT

Hmmm good thinking

Re: Qvd file not able create in incremental load

Anonymous — Wed, 13 Jan 2016 15:59:21 GMT

Hi Sunny,

Thanks for ur reply. It was my error. I have done, but it was not working.

Re: Qvd file not able create in incremental load

Anonymous — Wed, 13 Jan 2016 17:03:23 GMT

Hi Bill,

It should not override. it should append the new data to existing QVD data. Here daily my source file will be override, so even my Qvd also overriding with that particular data only. I am not able to see old data. But I want old data also.

Re: Qvd file not able create in incremental load

Anonymous — Wed, 13 Jan 2016 17:07:57 GMT

Having got your new incremental rows into a resident table you need to concatenate load the current qvd onto the resident table with a Not Exists on the table unique key. Then overwrite the qvd disc file.

This Blog Post by stevedark explains it in detail http://www.quickintelligence.co.uk/qlikview-incremental-load/

Re: Qvd file not able create in incremental load

Anonymous — Wed, 13 Jan 2016 17:11:46 GMT

This is fact table, here I have only inserts records, no updates.

Re: Qvd file not able create in incremental load

Anonymous — Wed, 13 Jan 2016 17:30:21 GMT

Yup, that is one of the simpler, yet standard, scenarios

In the blog http://www.quickintelligence.co.uk/qlikview-incremental-load/ have a look at the section Append Only Table With A Unique Sequential ID

Re: Qvd file not able create in incremental load

stevedark — Wed, 13 Jan 2016 18:07:45 GMT

Thanks for sharing the link Bill.

Re: Qvd file not able create in incremental load

stevedark — Wed, 13 Jan 2016 18:10:20 GMT

Your initial code is saying "if there is no QVD, then do nothing".

This will always result in no data.

Steve

Re: Qvd file not able create in incremental load

Anonymous — Thu, 14 Jan 2016 12:41:23 GMT

Thanks Steve.

In my script I am loading table data after If statement and storing that table as a QVD in STORE command. Can you explain me where it is going wrong.

Regards,

Kumar

Re: Qvd file not able create in incremental load

Peter_Cammaert — Thu, 14 Jan 2016 12:52:37 GMT

The problem lies in the return value of FileSize(). How should the function make a distinction between a file with 0 bytes (FileSize=0) and a file that doesn't even exist yet?

In the second case, FileSize() returns NULL. Your IF should test for both NULL (QVD doesn't exist) and 0 (QVD contains nothing = useless file)

Peter

Re: Qvd file not able create in incremental load

Anonymous — Thu, 14 Jan 2016 12:52:58 GMT

Could you share the whole load script, especially the bit after the STORE you refer to ?

Re: Qvd file not able create in incremental load

Anonymous — Thu, 14 Jan 2016 13:46:23 GMT

Hi Bill,

Let Vqvxpath = 'D:\QlikView\SourceDocuments\QVX\Mktg_Funnel_Prj\';

Let Vqvdpath = 'D:\QlikView\SourceDocuments\QVD\Mktg_Funnel_Prj\';

If FileSize('$(Vqvdpath)test.qvd') > 0 then

test1:

LOAD * FROM fact table;

Store test1into test.qvd(qvd);

DROP Table test1;

ENDIF.

Re: Qvd file not able create in incremental load

Anonymous — Thu, 14 Jan 2016 13:49:53 GMT

Hi Peter,

Here If I don't use IF statement QVD will be override with the latest data only, but i don't see any earlier data.

Regards,

Kumar

Re: Qvd file not able create in incremental load

Anonymous — Thu, 14 Jan 2016 14:08:48 GMT

Should there be a space between test1 and into instead of test1into

And should the Store command be against Vqvxpath, as in :

Store test1 into $(Vqvdpath)test.qvd(qvd);

Notwithstanding that, what this seems to be trying to do is :

If qvd file exists

then

Load data from source

Store this data into qvd file [thus overwriting existing qvd file]

Drop resident table

else

Do nothing

Is that what you wish ?

Re: Qvd file not able create in incremental load

Anonymous — Thu, 14 Jan 2016 14:34:16 GMT

What my actual requirement is.

Suppose I have a data like

date	Amount
18/12/2015	13
19/12/2015	14
20/12/2015	16

so my QVD will store this data.

So next this source will be override with the below data.

date amount

21/12/2015 20

So finally in my QVD I want to see all 4 rows.

Regards,

Kumar

Re: Qvd file not able create in incremental load

Anonymous — Thu, 14 Jan 2016 14:39:10 GMT

Are you hoping that the STORE command will concatenate the additional data onto an existing qvd.

If so then that is not how the STORE command works. It will overwrite the existing qvd with only the additional data, thus getting rid of the old data.

Re: Qvd file not able create in incremental load

Anonymous — Thu, 14 Jan 2016 15:05:13 GMT

I tried this way

test1:

LOAD * FROM

test.xlsx

(ooxml, embedded labels, table is Sheet1);

STORE test1 into C:\Users\Ramprasad\Desktop\Marketing funnel QVW\test.QVD(qvd);

If FileSize('C:\Users\Ramprasad\Desktop\Marketing funnel QVW/test.qvd') > 0 then

Concatenate(test1)

LOAD * From

C:\Users\Ramprasad\Desktop\Marketing funnel QVW\test.QVD(qvd);

DROP Table test1;

ENDIF