Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

mike_spada
Not applicable

Last month and date missing

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:

  

DateMyValue
07/09/201633
08/09/201611
09/09/201623
12/09/201644
13/09/201632
14/09/201643
09/10/201677
10/10/201665
11/10/201645

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

1 Solution

Accepted Solutions
simone_spanio
Not applicable

Re: Last month and date missing

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.

3 Replies
MarcoWedel
Not applicable

Re: Last month and date missing

maybe first loading

AddMonths(vTodayDate, -1)  as PreviousMonthDate

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

simone_spanio
Not applicable

Re: Last month and date missing

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
Not applicable

Re: Last month and date missing

Thanks Simone,

it was exactly what I wanted!

Mike