Repeating values until null.

[adnan_rafiq]

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

[maxgro]

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;

[settu_periasamy]

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;

[hic]

What Gysbert suggests should work.

If the values only repeat once and no more, then something else is wrong.

• Check that you use Peek() and not Previous().
• Check that the records are ordered correctly.
• Check that Peek() contains the created field - the output field:
  If(Len(Trim([Header 1]))>0,[Header 1], Peek([Header 1])) as [Header 1]

See also How to populate a sparsely populated field

HIC

[adnan_rafiq]

Here is excel and qvw.

[settu_periasamy]

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);

[Gysbert_Wassenaar · Accepted solution]

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]

[tamilarasu]

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:

[adnan_rafiq]

Thanks Gysbert 

can you explain the logic.

Please

[Gysbert_Wassenaar]

See this blog post: Peek() vs Previous() – When to Use Each