topic Re: Last month and date missing in QlikView

Last month and date missing

mike_spada — Thu, 03 Nov 2016 17:16:40 GMT

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

Re: Last month and date missing

MarcoWedel — Fri, 04 Nov 2016 00:30:55 GMT

maybe first loading

AddMonths(vTodayDate, -1) as PreviousMonthDate

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

Re: Last month and date missing

simospa — Fri, 04 Nov 2016 08:05:41 GMT

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 🙂

Re: Last month and date missing

mike_spada — Fri, 04 Nov 2016 08:27:13 GMT

Thanks Simone,

it was exactly what I wanted!

Mike