Peek back till not Null

[Simon_J]

Hi

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

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

[Anonymous]

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

[NareshGuntur · Accepted solution]

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@

[senpradip007]

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;   

[Simon_J]

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