Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Se attached, the script is quite simple.
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
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
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
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
So is in my example. You've missed something in your script, I assume.
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
Do you have a sample you can share?