Propagate values downwards

[pauljohansson]

Hi all happy Qlikview people,

I have a seemingly easy problem that i don't manage to solve.I want to populate the LastActiveWeek column downwards. I have been looking at Peek(). But since its impossible prehand to know how many rows I need to go back and check I cant get it to work.

Starting Point:

RegistrationDate	Customer	WeekDate	LastActiveWeek
2015-01-01	7949550	1	1
2015-01-01	7949550	2
2015-01-01	7949550	3	3
2015-01-01	7949550	4
2015-01-01	7949550	5	5
2015-01-01	7949550	6
2015-01-01	7949550	7
2015-01-01	7949550	8
2015-01-01	7949550	9
2015-01-01	7949550	10

Goal:

RegistrationDate	Customer	WeekDate	LastActiveWeek
2015-01-01	7949550	1	1
2015-01-01	7949550	2	1
2015-01-01	7949550	3	3
2015-01-01	7949550	4	3
2015-01-01	7949550	5	5
2015-01-01	7949550	6	5
2015-01-01	7949550	7	5
2015-01-01	7949550	8	5
2015-01-01	7949550	9	5
2015-01-01	7949550	10	5

What would the recommended technique be? Any guidance or ideas are very much appreciated.

Thanks in advance,

Br

Paul

Message was edited by: Paul Johansson

[Anonymous]

Se attached, the script is quite simple.

[sunny_talwar]

Table:

LOAD RegistrationDate,

     Customer,

     WeekDate,

     LastActiveWeek

FROM

[http://community.qlik.com/thread/151302]

(html, codepage is 1252, embedded labels, table is @1);

Table1:

NoConcatenate

LOAD RegistrationDate,

     Customer,

     WeekDate,

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

Resident Table;

DROP Table Table;

Hope this helps

Best,

S

[pauljohansson]

Hi Michael,

Thanks for your reply.

If you check my table, you see that there sometimes might be several rows without any value:

2015-01-01	7949550	5	5
2015-01-01	7949550	6
2015-01-01	7949550	7
2015-01-01	7949550	8
2015-01-01	7949550	9
2015-01-01	7949550	10

A Peek would result in:

2015-01-01	7949550	5	5
2015-01-01	7949550	6	5
2015-01-01	7949550	7
2015-01-01	7949550	8
2015-01-01	7949550	9
2015-01-01	7949550	10

leaving most of the rows empty,

br

Paul

[pauljohansson]

Hi Sunindia,

Thanks for your reply.

If you check my starting point table you see that there sometimes are several rows that are empty. So Peek the previous row will not solve the problem.

br

Paul

[sunny_talwar · Accepted solution]

Hey Paul,

This is the output from your data

So, Peek is going to fill in all the cells which are empty with the last given value above it.

HTH

Best,

S

[Anonymous]

So is in my example.  You've missed something in your script, I assume.

[delmak2000]

Hello, Sunny.....Can't seem to find the solution u provided to Paul....Pls help....

My case is a little bit similar.....

I want to propagate the last value if though the date for that data doesn't exist.

Thanks

[sunny_talwar]

Do you have a sample you can share?