Looping Through Newest to Oldest file and concatenate new records

[haymarketpaul]

QV11 SR4

I have a list of files in a folder with the following naming convention:-

MIMs Learning - Matched Records jan2016.xlsx

MIMs Learning - Matched Records dec2015.xlsx

MIMs Learning - Matched Records nov2015.xlsx

MIMs Learning - Matched Records oct2015.xlsx

I need to loop through from the newest to oldest file and concatenate new records only based on UserID

Each record within each file has a unique UserID

This should ensure i get the latest data for each UserID (each UserID could be in one or more files)

[jonathandienst]

Do the files get added once per month, or do they change during the month / after month end?

I they get added once per month, you could use an incremental load and only add records from the mots recent file. Load the rest from your incremental load qvd.

[haymarketpaul]

Hi

Yes just one new file is added a month.

Was i'm hoping to do something along those lines.  Just struggling a bit to piece it all together.  here's my thinking so far...

I was thinking i could use something like the following to get the latest filename

for each File in filelist ('H:\Circulation\Data ops\Medical\MIMS Learning - Binleys\MIMs Learning - Matched Records*.xlsx')

Folder:

Load '$(File)' as Name,

FileTime( '$(File)' ) as FileTime

autogenerate 1;

next File

LatestFile:

first 1

Load

Name,

FileTime,

1 as dummy

Resident Folder

Order By FileTime DESC;

drop table Folder;

and then something like this...to concatenate each new months data...i'll need to substitute the filename below with the one from the above LOAD i think...

LOAD

//CMS Users

     UserID,

Matched

FROM

(ooxml, embedded labels, table is Sheet1)

Where Len(UserID)>0;

//Existing users only - some are blank and irrelevant

Concatenate

//add previously stored data but exclude any that exist in the latest file (may have been updated)

LOAD

     UserID,

Matched

FROM

(qvd)

Where not exists (UserID)

;

//store resultant data to new QVD file for next month comparison

STORE BinleysMatched INTO $(vQVDPath)BinleysMatchedCMS.qvd;