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

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

Wed, 24 Jul 2013 12:31:10 GMT

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.

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

Wed, 24 Jul 2013 12:34:48 GMT

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

Gysbert_Wassenaar — Wed, 24 Jul 2013 12:41:16 GMT

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

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

Wed, 24 Jul 2013 12:43:42 GMT

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

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

Wed, 24 Jul 2013 13:46:00 GMT

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

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

stigchel — Wed, 24 Jul 2013 14:02:56 GMT

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

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

Wed, 24 Jul 2013 14:46:18 GMT

Thanks guys

That worked perfectly