Fill column value with previous not null value

[swati_rastogi27]

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

[sunny_talwar]

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?

[Chanty4u]

chk dis

Fill empty cells with value from "above"

[vikasmahajan]

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

HTH

Vikas

[swati_rastogi27]

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

[sunny_talwar · Accepted solution]

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;

[saranyadurai]

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

[sunny_talwar]

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?

[saranyadurai]

yes