topic Re: Fill column value with previous not null value in QlikView

Fill column value with previous not null value

swati_rastogi27 — Wed, 16 Mar 2016 09:47:23 GMT

Hi folks,

The requirement is :

I have 4 columns in my transactional table , C1,C2,C3,C4

If on any particular day I do not receive a value for either of the columns,I need to fetch corresponding value from the previous row.

Especially, on weekends(Saturday and Sunday) I do not receive data for any of the columns, so I need to populate previous day(Friday) values.

Plz help.

Thanks

Re: Fill column value with previous not null value

sunny_talwar — Wed, 16 Mar 2016 09:58:32 GMT

If you have the dates already available in your database you can use Peek()/Previous() function to do it. Do you have a sample where it can be demonstrated?

Re: Fill column value with previous not null value

Chanty4u — Wed, 16 Mar 2016 10:01:52 GMT

chk dis

Fill empty cells with value from "above"

Re: Fill column value with previous not null value

vikasmahajan — Wed, 16 Mar 2016 10:06:20 GMT

Please go through https://community.qlik.com/docs/DOC-7142

HTH

Vikas

Re: Fill column value with previous not null value

swati_rastogi27 — Thu, 17 Mar 2016 07:59:37 GMT

A sample data set is :

Required output is:

We usually do not receive data for weekends/public holidays,still we need to populate as mentioned above

Re: Fill column value with previous not null value

sunny_talwar — Thu, 17 Mar 2016 14:44:46 GMT

Try this:

Table:

LOAD * Inline [

Col1, Col2, Col3, Col4

3/3/2016, 12, 13, 14

4/3/2016, , 23, 24

7/3/2016, 32, ,

8/3/2016, 42, 43, 44

];

Join (Table)

LOAD Date(Min + IterNo() - 1) as Col1

While Min + IterNo() - 1 < Max;

LOAD Max(Col1) as Max,

Min(Col1) as Min

Resident Table;

FinalTable:

NoConcatenate

LOAD Col1,

If(Len(Trim(Col2)) = 0, Peek('Col2'), Col2) as Col2,

If(Len(Trim(Col3)) = 0, Peek('Col3'), Col3) as Col3,

If(Len(Trim(Col4)) = 0, Peek('Col4'), Col4) as Col4

Resident Table

Order By Col1;

DROP Table Table;

Re: Fill column value with previous not null value

saranyadurai — Mon, 27 Feb 2017 04:36:56 GMT

Hi,

I have four columns

Date Category Sub-Category Value

1.1.2017 A a 200

1.1.2017 B b 300

1.1.2017 C c 200

2.1.2017 A d 500

2.1.2017 B e 400

My Output is:

I want to generate the line of C Category with previous value.....

help me...

Thank You

Re: Fill column value with previous not null value

sunny_talwar — Mon, 27 Feb 2017 10:20:35 GMT

So you want to add another row for 2.1.2017 with category as C and Value as 200? What will be the Sub-Category? c also?

Re: Fill column value with previous not null value

saranyadurai — Mon, 27 Feb 2017 11:19:05 GMT

yes