Last month and date missing

[mike_spada]

Hi,

I have a table with date and values. In loading script I need to take the value referred to the date 1 month before... this could be simple with AddMonths function (and I'm able to to this 🙂 ) but what if the date is missing and I need to recover the previous populated date?

For example:

Date	MyValue
07/09/2016	33
08/09/2016	11
09/09/2016	23
12/09/2016	44
13/09/2016	32
14/09/2016	43
09/10/2016	77
10/10/2016	65
11/10/2016	45

with vTodayDate = 11/10/2016, 1 month before is AddMonths(vTodayDate, -1) --> 11/09/2016... but I haven't it. I need to consider 09/09/2016 because is the previous useful date.

How can I do this?

Any idea?

Thanks in advance,

Mike

[MarcoWedel]

maybe first loading

AddMonths(vTodayDate, -1)  as PreviousMonthDate

and in a second resident load join the Maximum Date <= PreviousMonthDate.

[simospa · Accepted solution]

I agree with Marco,

maybe you could try with first n load (because you don't know how many dates are missing... one? two? Ten?).

If DataTable is the name of the table loaded, you could do something like this:

SET vToday = Date('11/10/2016', 'DD/MM/YYYY');

MyTable:

LOAD * INLINE [

    Date, MyValue

    07/09/2016, 33

    08/09/2016, 11

    09/09/2016, 23

    12/09/2016, 44

    13/09/2016, 32

    14/09/2016, 43

    09/10/2016, 77

    10/10/2016, 65

    11/10/2016, 45

];

NoConcatenate

AMonthAgo:

FIRST 10 LOAD Date, //if you need you can increase the number of record (for example if you have many days of jump)

  MyValue

Resident MyTable

Where Date <= AddMonths($(vToday), -1);

Inner Join

LOAD Max(Date) as Date

Resident AMonthAgo;

DROP Table MyTable;

Let us know if what you are looking for 🙂

S.

[mike_spada]

Thanks Simone,

it was exactly what I wanted!

Mike