topic Re: Incremental Load - Append in QlikView

Incremental Load - Append

kevbrown — Wed, 25 Nov 2020 16:16:04 GMT

I have the following script that loads in anything that starts with "Revenue" Every month I'll have a new Revenue file and I only want to load that file, what's the best way to write the script?

Let vQVDPath='\\gblonfc11\mc-glob\UK National Placement MI\Kev Brown\Reload Dashboard\2. Dashboard Reload\3. QVD Output\';

Revenue:
LOAD
FileName() AS File,
COVERAGE_EFFECTIVE_DT,
[Coverage Year],
[Coverage Month],
[Coverage Day],
[Revenue Year]

FROM
$(vQVDPath)Revenue*.qvd
(qvd);

Thanks

Re: Incremental Load - Append

beck_bakytbek — Mon, 17 Jul 2017 14:52:43 GMT

Hi Kevin,

look at this: https://www.youtube.com/watch?v=BoEhqyy2xbM

i hope that helps

Beck

Re: Incremental Load - Append

marcus_malinow — Mon, 17 Jul 2017 15:04:37 GMT

How about this?

for each file in FileList('$(vQVDPath)Revenue*.qvd')

FileList:
Load
'$(file)' as file,
QvdCreateTime('$(file)') as cre
AUTOGENERATE 1;
next file

RIGHT KEEP (FileList)
LOAD
max(cre) as cre
RESIDENT FileList;

LET vLatestFileName = peek('file', -1);

DROP TABLE FileList;

Revenue:
LOAD
FileName() AS File,
COVERAGE_EFFECTIVE_DT,
[Coverage Year],
[Coverage Month],
[Coverage Day],
[Revenue Year]

FROM
[$(vLatestFileName)]
(qvd);

Re: Incremental Load - Append

effinty2112 — Mon, 17 Jul 2017 15:20:47 GMT

Hi Kevin,

Try:

QvdTableHeader:

LOAD

FileName( ) as FileName,

CreateUtcTime

FROM $(vQVDPath)\*.qvd (XmlSimple, Table is [QvdTableHeader]);

[Latest File]:

LOAD

FirstSortedValue(FileName, -CreateUtcTime) as FileName

Resident QvdTableHeader;

Let vFileName = Peek('FileName',0,'Latest File');

Drop Tables QvdTableHeader,[Latest File];

Data:

LOAD *

FROM

[$(vQVDPath)\$(vFileName)]

(qvd);

This will load from the qvd with the latest CreateUtcTime.

Cheers

Andrew

Re: Incremental Load - Append

kevbrown — Tue, 18 Jul 2017 14:43:35 GMT

That's great, thank you

Re: Incremental Load - Append

effinty2112 — Tue, 18 Jul 2017 14:45:04 GMT

Glad it works for you!