topic Re: Place the value when data does not exist to consecutive months data. in QlikView

Place the value when data does not exist to consecutive months data.

Mon, 03 Oct 2011 09:58:57 GMT

Hi All,

I need a report, where the date should be placed to consecutive month if that month data is zero or null.

For Example:

Data:

Month Sales

Jan 100

Feb 0

Mar 200

Apr 230

May 140

Jun 0

Jul 0

Aug 120

...

Etc

In this case For Feb month the value should be 100(Jan Data) and Jun and Jul should be replaced with May data (140), the same results should be appear in pivot table.

Please let me know in case you need any more information on it.

Regards,

Ravi.

Re: Place the value when data does not exist to consecutive months data.

Mon, 03 Oct 2011 10:55:14 GMT

Hi,

I hope this helps u.

C u,

Stefano.

Place the value when data does not exist to consecutive months data.

Anonymous — Mon, 03 Oct 2011 11:21:30 GMT

Hi Ravi,

You could also maybe solve it by using the Peek() function in your loading script.

Check this: http://community.qlik.com/message/154446#154446

Good luck.

Dennis.

Re: Place the value when data does not exist to consecutive months data.

Mon, 03 Oct 2011 12:10:56 GMT

Place the value when data does not exist to consecutive months data.

Anonymous — Mon, 03 Oct 2011 12:57:52 GMT

If this is your data in fe Excel:

Month Sales

Jan 100

Feb

Mrt 200

Apr 230

Mei 140

Jun

Jul

Aug 150

Sep

Okt

Nov

Dec 200

You can use a load script like below:"

Directory;

LOAD Month,

IF(ISNULL(Sales), Peek(Sales), Sales )as Sales

FROM

[example (1).xls]

(biff, embedded labels, table is Tab$);

This Will generate this:

Jan	100
Feb	100
Mrt	200
Apr	230
Mei	140
Jun	140
Jul	140
Aug	150
Sep	150
Okt	150
Nov	150
Dec	200

And you can use this in any object or charts

Place the value when data does not exist to consecutive months data.

Tue, 04 Oct 2011 11:55:43 GMT

Jan	100
Feb	100
Mrt	200
Apr	230
Mei	140
Jun	140
Jul	140
Aug	150
Sep	150
Okt	150
Nov	150
Dec	200

Jan	100
Feb	100
Mrt	200
Apr	230
Mei	140
Jun	140
Jul	140
Aug	150
Sep	150
Okt	150
Nov	150
Dec	200

Jan	100
Feb	100
Mrt	200
Apr	230
Mei	140
Jun	140
Jul	140
Aug	150
Sep	150
Okt	150
Nov	150
Dec	200