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

[#1]

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.

[#2]

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);

[Gysbert_Wassenaar]

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

[#4]

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

[#5]

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 · Accepted solution]

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);

[#7]

Thanks guys

That worked perfectly