Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
pepe2209
Creator
Creator

Select previous value of record if record is empty

Hello

please look at the table:

IDData
1A
2A
3
4B
5
6B
7
8

This is a dataset with missing values. and I would like to achieve in the script that if the value is empty it automatically fills in the previous value.

So for ID 3, Data should be A (according to ID 2)

     for ID 5, Data should be B (according to ID 4)

     for ID 7, Data should be B (according to ID 6)

and for ID 8, Data should be B (according to automatically generated ID 7 or ID 6?)

Is this possible to generate in the script? (maybe previous() or peek() statement?)

Thanks

1 Solution

Accepted Solutions
Not applicable

Please use below code;

LOAD ID,

     IF(isnull(Data),Peek(DATA),Data) as DATA

FROM

(ooxml, embedded labels, table is Sheet1);

View solution in original post

6 Replies
Not applicable

Yes, use either peek('Data') or previous(Data) associated to isnull:

if(isnull(Data), previous(Data), Data) as Data2

I would load first the data in a temp table that I would sort (to be sure of the order of the ID) into another table

Fabrice

Not applicable

Please use below code;

LOAD ID,

     IF(isnull(Data),Peek(DATA),Data) as DATA

FROM

(ooxml, embedded labels, table is Sheet1);

View solution in original post

Not applicable

Hi Previous will not work ..
Only peek will work like below

if(isnull(Data), Peek(Data2), Data) as Data2

pepe2209
Creator
Creator
Author

Yes this works. Thanks to both of you.

the isNull part was new to me.

Thanks again

preminqlik
Specialist II
Specialist II

hi there,

find attachment.

regards

Prem

Not applicable

This solution doesn't work if the first record readed contains null.

You could add this in the script before the calculation to use the value of the lower ID with not null Value

    

     -- Calculate the lower ID with not null Value

     MinIDWithData:

     load min(id) as ID resident Table1 where not IsNull(Data);

     let v_MinIDWithData = peek('ID', 0, 'MinIDWithData');

     drop table MinIDWithData;

     -- Calculate the Value

     Aux:

     load Data resident Table1 where ID = $(v_MinIDWithData);

     let v_aux = peek('Data', 0, 'Aux');

     drop table Aux;

load.....

     IF(isnull(Data),if(isnull(Peek(DATA), $(v_aux), Peek(DATA)),Data) as DATA

......