Adding future data using loops into table in qlikview

[#1]

Hi

I have a data in y table like below

   Date                                                         sales

2016-03-01 00:00:00.000                                  45

2016-04-01 00:00:00.000                                   58

2016-05-01 00:00:00.000                                   52

2016-06-01 00:00:00.000                                   528

2016-07-01 00:00:00.000                                   63

2016-08-01 00:00:00.000                                 85

2016-09-01 00:00:00.000                                   52

2016-10-01 00:00:00.000                                  310

2016-11-01 00:00:00.000                               85

2016-12-01 00:00:00.000                                   21

2015-01-01 00:00:00.000                                  85

2015-02-01 00:00:00.000                                  98

.. and some more old date

while loading into qlikview i want to add dates upto 2020-03-01 00:00:00.000 and sales as 0.

How to achieve this ?

Please do the needful

Thanks

Thiru

[Anonymous]

Hi Thiru Palu,

Please check the attached file for reference.

I hope it helps.

-- Karla

[#3]

Hi

Attached Missed.

Thiru

[Anonymous]

Hi Thiru Palu,

I'm attaching the file again, just in case there are still issues with the file, here is the code:

Data:

LOAD

  Date#(Date, 'YYYY-DD-MM HH:mm:ss[.fff]') as Date,

  Sales;

LOAD * INLINE [

    Date, Sales

    2016-03-01 00:00:00.000, 45

    2016-04-01 00:00:00.000, 58

    2016-05-01 00:00:00.000, 52

    2016-06-01 00:00:00.000, 528

    2016-07-01 00:00:00.000, 63

    2016-08-01 00:00:00.000, 85

    2016-09-01 00:00:00.000, 52

    2016-10-01 00:00:00.000, 310

    2016-11-01 00:00:00.000, 85

    2016-12-01 00:00:00.000, 21

    2015-01-01 00:00:00.000, 85

    2015-02-01 00:00:00.000, 98

];

/***Getting max date****/

MaxTimestamp:

LOAD

  Max(Date) as MaxTime

Resident Data;

LET vMinDate = Floor(Peek('MaxTime',0,'MaxTimestamp'));

LET vMaxDate = num(Date#('2020-03-01','YYYY-MM-DD'));

/******Generate additional dates*****/

LOAD

  Timestamp($(vMinDate) + IterNo(), 'YYYY-DD-MM HH:mm:ss[.fff]') as Date,

  0 as Sales

Autogenerate 1 while $(vMinDate) + IterNo() <= $(vMaxDate);

Kind Regards,

-- Karla

[#5]

Hi Karla,

Thanks it's working

Thiru