Loading latest data from overlapping files

[adamdavi3s]

Hi All,

There MUST be a simple way to do this but I am having a brain malfunction today....

We have a set of data files which always load the last three months data as there can be a lag in the data being updated. How can I just load the latest set? E.g. We load three months every time, green shows finalised and orange shows draft but we would still want to report on draft where no finalised data exists.

File Date	Jan	Feb	Mar	Apr	May
02/04/2013	Final	Draft	Draft
25/05/2013		Final	Draft	Draft
06/06/2013			Final	Draft	Draft

So for example if I ran a report after 25 May I would want to see:

Jan from the April file

Feb from the May file

Mar from the May file

Then if I ran it after 06 June I would want to see:

Jan from April

Feb from May

March from June

Hopefully that makes sense!

[adamdavi3s]

I guess I could map the combination of data_date + max(file date) and use this somehow....

[adamdavi3s · Accepted solution]

So yes that worked, if anyone else comes across this:

within the table:

load filedate & datadate as id

load a temp mapping table of:

mapdatadate,

max(filedate) as maxfiledate

load a final mapping table of data:

mapdatadate & maxfiledate as identifier

then just do an inner keep where exists(identifier,id)