topic Re: Repeating values until null. in QlikView

Repeating values until null.

adnan_rafiq — Tue, 29 Mar 2016 07:20:54 GMT

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

Re: Repeating values until null.

Gysbert_Wassenaar — Tue, 29 Mar 2016 07:23:12 GMT

LOAD

If(Len(Trim([Header 1]))>0,[Header 1], Peek([Header 1]) as [Header 1],

[Header 2]

FROM

...

If [Header 1] contains only numbers you could also try Alt([Header 1], peek([Header 1])) as [Header 1]

Re: Repeating values until null.

tamilarasu — Tue, 29 Mar 2016 07:26:55 GMT

You can use fill option in Transformation Wizard.

LOAD [Header 1],

[Header 2]

FROM

[Sample.xlsx]

(ooxml, embedded labels, table is Sheet1, filters(

Replace(1, top, StrCnd(null))

));

Re: Repeating values until null.

adnan_rafiq — Tue, 29 Mar 2016 07:33:01 GMT

Same result that I had already had the values repeat 1 time and then stop. see here

Re: Repeating values until null.

Gysbert_Wassenaar — Tue, 29 Mar 2016 07:35:32 GMT

Post your qlikview document and source file.

Re: Repeating values until null.

Kushal_Chawda — Tue, 29 Mar 2016 07:39:09 GMT

try this

Load *,

if( len(trim(Header1))=0, peek('Header1_1'),Header1) as Header1_1

FROm Table

Re: Repeating values until null.

adnan_rafiq — Tue, 29 Mar 2016 07:39:31 GMT

I want to do it via script.

Re: Repeating values until null.

adnan_rafiq — Tue, 29 Mar 2016 07:40:14 GMT

see reply above

Re: Repeating values until null.

tamilarasu — Tue, 29 Mar 2016 07:42:22 GMT

The above code is load script.!!

Re: Repeating values until null.

Kushal_Chawda — Tue, 29 Mar 2016 07:46:41 GMT

what reply?

Re: Repeating values until null.

maxgro — Tue, 29 Mar 2016 07:46:53 GMT

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;

Re: Repeating values until null.

settu_periasamy — Tue, 29 Mar 2016 07:48:23 GMT

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;

Re: Repeating values until null.

hic — Tue, 29 Mar 2016 07:49:17 GMT

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]

HIC

Re: Repeating values until null.

adnan_rafiq — Tue, 29 Mar 2016 07:51:56 GMT

Here is excel and qvw.

Re: Repeating values until null.

settu_periasamy — Tue, 29 Mar 2016 07:56:51 GMT

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

Re: Repeating values until null.

Gysbert_Wassenaar — Tue, 29 Mar 2016 07:57:32 GMT

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]

Re: Repeating values until null.

tamilarasu — Tue, 29 Mar 2016 07:59:20 GMT

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:

Re: Repeating values until null.

adnan_rafiq — Tue, 29 Mar 2016 08:04:37 GMT

Thanks Gysbert

can you explain the logic.

Please

Re: Repeating values until null.

Gysbert_Wassenaar — Tue, 29 Mar 2016 08:06:36 GMT

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