topic Re: Peek back till not Null in QlikView

Peek back till not Null

Simon_J — Mon, 09 Dec 2013 14:24:56 GMT

I have a problem where i need to look back into a set of data to bring back the latest result

The data looks like the following

ID Number	Station	Start Station	End Station
101	Station 1	Station 1	-
102	Station 2	-	-
103	Station 3	-	-
104	Station 4	-	-
105	Station 5	-	-
106	Station 6	-	-
107	Station 7	-	Station 7
106	Station 6	Station 6	-
105	Station 5	-	-
104	Station 4	-	Station 4

I need to be able to fill in the blanks where i would look back and get the start and end of each journey. The Journey would be a combination of the two.

This would be like the following:

ID Number	Station	Start Station	End Station	Journey
101	Station 1	Station 1	Station 7	Station 1 - Station 7
102	Station 2	Station 1	Station 7	Station 1 - Station 7
103	Station 3	Station 1	Station 7	Station 1 - Station 7
104	Station 4	Station 1	Station 7	Station 1 - Station 7
105	Station 5	Station 1	Station 7	Station 1 - Station 7
106	Station 6	Station 1	Station 7	Station 1 - Station 7
107	Station 7	Station 1	Station 7	Station 1 - Station 7
106	Station 6	Station 6	Station 4	Station 6 - Station 4
105	Station 5	Station 6	Station 4	Station 6 - Station 4
104	Station 4	Station 6	Station 4	Station 6 - Station 4

I was going to use a peek function but would need to look back only as far as the next entry. Is there a way to do that?

Many Thanks,

Simon

Re: Peek back till not Null

Anonymous — Mon, 09 Dec 2013 18:26:33 GMT

Simon

Have a look at this pdf Generating missing data.pdf by HIC from this technical brief Generating Missing Data In QlikView.

Read pages 6 & 7 and you may well find the solution you need.

Best Regards, Bill

Re: Peek back till not Null

NareshGuntur — Tue, 10 Dec 2013 07:13:42 GMT

Hey Simon,

Tried with peek function.

Test:

LOAD [ID Number],

Station,

[Start Station],

[End Station],

Journey

FROM

C:\Users\nguntur\Desktop\peektillnull.xlsx

(ooxml, embedded labels, table is Sheet1);

STORE Test into Test.qvd(qvd);

LOAD

if(IsNull([Start Station]),Peek([NewStation]),[Start Station]) as NewStation

Resident Test;

DROP Table Test;

When you load the first value, park it in a field "NewStation" so that later when [Start Station] is null, it will get the value from "NewStation"

Thanks,

Naresh@

Re: Peek back till not Null

senpradip007 — Tue, 10 Dec 2013 11:18:13 GMT

Try this:

WWW:

LOAD * Inline [

ID_Number, Station, Start_Station, End_Station, Journey

101, Station 1, Station 1, -, -

102, Station 2, -, -, -

103, Station 3, -, -, -

104, Station 4, -, -, -

105, Station 5, -, -, -

106, Station 6, -, -, -

107, Station 7, -, Station 7, -

106, Station 6, Station 6, -, -

105, Station 5, -, -, -

104, Station 4, -, Station 4, -

];

W1:

NoConcatenate

LOAD

if(Start_Station = '-',Peek([NewStation]),Start_Station) as NewStation,

RowNo() As RowId

Resident WWW;

DROP Table WWW;

WWW:

NoConcatenate

LOAD

if(End_Station = '-',Peek(NewStation1),End_Station) as NewStation1

Resident W1

Order by RowId desc;

DROP Table W1;

Final:

NoConcatenate

LOAD

NewStation & ' - ' & NewStation1 As JourneyNew

Resident WWW

Order By RowId;

DROP Table WWW;

DROP Fields Start_Station, End_Station, Journey;

Re: Peek back till not Null

Simon_J — Wed, 11 Dec 2013 11:56:50 GMT

HI All,

Thank you for all your help. I have just tried the code Naresh supplied and it work for me. It Peeks back and brings back the instance i need.

Thanks Again,

Simon