topic Loading latest data from overlapping files in QlikView

Loading latest data from overlapping files

adamdavi3s — Mon, 14 Oct 2013 13:54:43 GMT

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!

Re: Loading latest data from overlapping files

adamdavi3s — Mon, 14 Oct 2013 15:03:43 GMT

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

Re: Loading latest data from overlapping files

adamdavi3s — Tue, 15 Oct 2013 08:55:59 GMT

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)