Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);
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);
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
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
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
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);
Thanks guys
That worked perfectly