Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

How can I load from QVD files using an incremental load based on File Date

Hi guys,

I have a quick question.

I have this QVD files that look like this SalesEntry_20130401.qvd

                                                        SalesEntry  20130402.qvd

                                                        SalesEntry  20130403.qvd

I will like to concatenate load the files in a master SalesEntry.qvd that will run each day and only select the files newer than the last reload time.

Many thanks for anyone that can help.

1 Solution

Accepted Solutions
stigchel
Honored Contributor

Re: How can I load from QVD files using an incremental load based on File Date

You can try something like the following:

DatesOfFiles:

LOAD MaxString(Mid(FileBaseName(),12, 8)) AS PossibleDates

FROM

*.qvd

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LET vLastDate = Peek('PossibleDates', -1);

LET file='SalesEntry_'&vLastDate&'.qvd';

DROP TABLE DatesOfFiles;

Load *

FROM

$(file)

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

6 Replies
Not applicable

Re: How can I load from QVD files using an incremental load based on File Date

try accoding to this example

MASTER:

LOAD pid,

     pname,

     price

FROM

(qvd);

TEMP:

LOAD MAX(pid) AS MAXPID

    Resident MASTER;

DROP Table MASTER;   

LET Vmax = Peek('MAXPID',-1,'TEMP');

TRANSACTION:

LOAD pid,

     pname,

     price

FROM

(ooxml, embedded labels, table is TRANSACTION) Where pid >$(Vmax);

Join

LOAD pid,

     pname,

     price

FROM

(qvd) Where NOT Exists(pid);

Re: How can I load from QVD files using an incremental load based on File Date

It's easiest if you can move the daily qvd files after you've loaded them. See here for example. Otherwise you'll have to do something based on parts of the filename. See here. Or the links in this discussion: Dynamic Update and Incremental Loading


talk is cheap, supply exceeds demand
Not applicable

Re: How can I load from QVD files using an incremental load based on File Date

Hi vishwaranjan

Thanks for your quick response. Unfortunately this is not the case because and this may sound strange I there is no primary key in this files so I have to do it by the date from file name.

Thanks again

Not applicable

Re: How can I load from QVD files using an incremental load based on File Date

Thanks Gysbert,

I looked at your examples and non of them fits.

I was thinking more like 

Set vConcatenate = ;

sub ScanFolder(Root)

          for each FileExtension in 'qvd'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

                              FileList:

                              $(vConcatenate)

                              LOAD '$(FoundFile)' as SourceFile,QvdCreateTime('$(FoundFile)') as CreateTime

                              FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

                                   If QvdCreateTime('$(FoundFile)') < '$(vThisExecTime)'

                                   MasterSalesEntry:

                                   LOAD *,

                                   FROM   $(FoundFile) (qvd);

                                   Endif

                              Set vConcatenate = Concatenate;

                              TRACE $(vConcatenate);

                    next FoundFile

          next FileExtension

end sub

Call ScanFolder('D:\Work\QlikView\TLC\Qlikview Master - 2009 R2 - TLC\Data\QVD') ;

STORE MasterSalesEntry INTO $(vDataFolder)MasterSalesEntry.qvd (qvd);

But

                                   If QvdCreateTime('$(FoundFile)') < '$(vThisExecTime)'

                                   MasterSalesEntry:

                                   LOAD *,

                                   FROM   $(FoundFile) (qvd);

                                   Endif

statement does not seems to work.

Thanks

stigchel
Honored Contributor

Re: How can I load from QVD files using an incremental load based on File Date

You can try something like the following:

DatesOfFiles:

LOAD MaxString(Mid(FileBaseName(),12, 8)) AS PossibleDates

FROM

*.qvd

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

LET vLastDate = Peek('PossibleDates', -1);

LET file='SalesEntry_'&vLastDate&'.qvd';

DROP TABLE DatesOfFiles;

Load *

FROM

$(file)

(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Not applicable

Re: How can I load from QVD files using an incremental load based on File Date

Thanks guys

That worked perfectly

Community Browser