topic Adding future data using loops into table in qlikview in QlikView

Adding future data using loops into table in qlikview

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Adding future data using loops into table in qlikview

Anonymous — Sat, 18 Jun 2016 03:46:52 GMT

Hi Thiru Palu,

Please check the attached file for reference.

I hope it helps.

-- Karla

Re: Adding future data using loops into table in qlikview

Mon, 20 Jun 2016 06:15:51 GMT

Attached Missed.

Thiru

Re: Adding future data using loops into table in qlikview

Anonymous — Mon, 20 Jun 2016 22:37:33 GMT

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

Re: Adding future data using loops into table in qlikview

Tue, 21 Jun 2016 06:23:26 GMT

Hi Karla,

Thanks it's working

Thiru