Qlik Community

QlikView Documents

Documents for QlikView related information.

Peek till not null

nareshguntur
Valued Contributor

Peek till not null

I see this question getting repeated. So, thought of posting it here.

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

The Start Station should be filled with the Previous Station when it is blank and if the previous station as well is blank, it should take one more step back.

//Script Start

//Loading the data

Test:

LOAD [ID Number],

     Station,

     [Start Station],

     [End Station],

     Journey

FROM

peektillnull.xlsx

(ooxml, embedded labels, table is Sheet1);

//note the difference between [Start Station] & StartStation

//StartStation is the new field which we are going to populate

Test1:

LOAD

*,

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

Resident Test;

//Script end

Here, StartStation is the new field that is getting created with the original value for the first time. Say Station 1.

And from here, whenever [Start Station] has a null value, it'll get peek(StartStation), the new field which has value "Station 1" and will be stored in the same new field StartStation.

And it doesn't matter how many rows have null value, it should go only one step back.

After this load statement is executed, StartStation will have all the rows filled with the previous value.

//Script start

DROP Table Test;

RENAME Table Test1 to Test;

//Script end

Drop the first table and rename the latest table as Test.

I have answered this here https://community.qlik.com/message/434207#434207

I have enclosed the source data and the qvw here.

Cheers,

Naresh

Tags (2)
Attachments
Version history
Revision #:
1 of 1
Last update:
‎11-23-2015 09:38 AM
Updated by: