Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER
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
Champion III
Champion III

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