Incremental Load - Append

[kevbrown]

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

[beck_bakytbek]

Hi Kevin,

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

i hope that helps

Beck

[marcus_malinow]

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

[effinty2112 · Accepted solution]

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

[kevbrown]

That's great, thank you

[effinty2112]

Glad it works for you!