topic Re: Propagate values downwards in QlikView

Propagate values downwards

pauljohansson — Mon, 09 Feb 2015 15:01:31 GMT

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,

Paul

Message was edited by: Paul Johansson

Re: Propagate values downwards

Anonymous — Mon, 09 Feb 2015 15:08:48 GMT

Se attached, the script is quite simple.

Re: Propagate values downwards

sunny_talwar — Mon, 09 Feb 2015 15:25:22 GMT

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,

Re: Propagate values downwards

pauljohansson — Mon, 09 Feb 2015 15:44:18 GMT

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,

Paul

Re: Propagate values downwards

pauljohansson — Mon, 09 Feb 2015 15:46:06 GMT

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.

Paul

Re: Propagate values downwards

sunny_talwar — Mon, 09 Feb 2015 15:50:09 GMT

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,

Re: Propagate values downwards

Anonymous — Mon, 09 Feb 2015 15:50:32 GMT

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

Re: Propagate values downwards

delmak2000 — Thu, 28 Jan 2016 18:51:08 GMT

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

Re: Propagate values downwards

sunny_talwar — Thu, 28 Jan 2016 19:23:00 GMT

Do you have a sample you can share?