Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone.
Just wanted to pick the brains of the gurus of QV. I want to repeat a value in a column that I get in a table using script, as many times until I find a non-null value in the same a column. So for example sake consider (AS-is)
Header 1 | Header 2 |
---|---|
42186 | AB |
CD | |
WE | |
TT | |
RR | |
QS | |
EF | |
42155 | GH |
IJ | |
KL | |
SA | |
AS | |
QW | |
MN |
I am trying to get, (To Be)
Header 1 | Header 2 |
---|---|
42186 | AB |
42186 | CD |
42186 | WE |
42186 | TT |
42186 | RR |
42186 | QS |
42155 | GH |
42155 | IJ |
42155 | KL |
42155 | SA |
42155 | |
42155 |
I have tried ALT, PEEK and PREVIOUS but only got values to repeat twice by using PREVIOUS.
Please help
It seems that previous suggestion work; this is the result with the table you posted
Source:
LOAD
rowno() as id,
[Header 1],
[Header 2]
FROM
[https://community.qlik.com/thread/211171]
(html, codepage is 1252, embedded labels, table is @1);
Final:
NoConcatenate LOAD
If(Len(Trim([Header 1]))>0,[Header 1], Peek([Header 1])) as [Header 1],
[Header 2]
Resident Source
Order By id;
DROP Table Source;
I think, Peek is working fine..
LOAD RecNo() as Key,
if( len(trim([Header 1]))=0, peek([Header 1]),[Header 1]) as [Header 1],
[Header 2]
FROM
[https://community.qlik.com/thread/211171]
(html, codepage is 1252, embedded labels, table is @1) where Len([Header 2])>0;
What Gysbert suggests should work.
If the values only repeat once and no more, then something else is wrong.
See also How to populate a sparsely populated field
HIC
Here is excel and qvw.
As Mentioned by HIC, you need to use the output field inside the Peek.
LOAD [Report number],
Column,
Numbers,
if(len(trim([Report number]))=0,Peek([Peekedcolumn]),[Report number]) as Peekedcolumn
FROM
[Book1 (3).xlsx]
(ooxml, embedded labels, table is Sheet1);
LOAD if(len(trim([Report number]))=0,Peek([Report number]),[Report number]) as Peekedcolumn,
Column,
Numbers
FROM
...
You're peeking in the wrong field. Use peek(Peekedcolumn) or give the resulting field the name [Report number]
Another solution (Fill option (Transformation Wizard))
LOAD [Report number],
Column,
Numbers
FROM
[Book1 (1).xlsx]
(ooxml, embedded labels, table is Sheet1, filters(
Replace(1, top, StrCnd(null))
));
Output:
Thanks Gysbert
can you explain the logic.
Please
See this blog post: Peek() vs Previous() – When to Use Each