Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Simon_J
Partner
Partner

Peek back till not Null

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 NumberStationStart StationEnd StationJourney

101

Station 1

Station 1-
102Station 2

-

-
103Station 3--
104Station 4--
105Station 5--
106Station 6--
107Station 7-Station 7

106

Station 6Station 6-
105Station 5--
104Station 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 NumberStationStart StationEnd StationJourney

101

Station 1

Station 1Station 7Station 1 - Station 7
102Station 2

Station 1

Station 7Station 1 - Station 7
103Station 3Station 1Station 7Station 1 - Station 7
104Station 4Station 1Station 7Station 1 - Station 7
105Station 5Station 1Station 7Station 1 - Station 7
106Station 6Station 1Station 7Station 1 - Station 7
107Station 7Station 1Station 7Station 1 - Station 7

106

Station 6Station 6Station 4Station 6 - Station 4
105Station 5Station 6Station 4Station 6 - Station 4
104Station 4Station 6Station 4Station 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

1 Solution

Accepted Solutions
NareshGuntur
Partner
Partner

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@

View solution in original post

4 Replies
bill_markham

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

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@

View solution in original post

senpradip007
Specialist III
Specialist III

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

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