topic Re: Repeat Values Loading in QlikView

Repeat Values Loading

vinicius_siquei — Wed, 26 Nov 2014 15:09:02 GMT

I'm loading a table that doesn't have data for all the rows, like below:

DateID, Rate

10, 0.15

11,

12, 0.17

13,

14,

15, 0.38

For the rows that Column Rate is null, I must repeat last valid value.

I tried to do this by using previous command, However it doesn't works in cases when I have 2 null records in sequence.

The expected result is:

DateID, Rate

10, 0.15

11, 0.15

12, 0.17

13, 0.17

14, 0.17

15, 0.38

Any Ideias ?

Thanks

Re: Repeat Values Loading

maxgro — Wed, 26 Nov 2014 15:13:16 GMT

load

.....,

if(len(trim(Rate))=0, peek(Rate), Rate) as Rate

....

resident ...

order by DateID

load * inline [

DateID, Rate

10, 0.15

11,

12, 0.17

13,

14,

15, 0.38

];

NoConcatenate

load DateID, if(len(trim(Rate))=0, Peek(Rate), Rate) as Rate

Resident a;

drop table a;

Re: Repeat Values Loading

Wed, 26 Nov 2014 15:20:02 GMT

Hi,

Try using resident load where u can put a condition on the field to replace Null !

Cheers !

Re: Repeat Values Loading

vinicius_siquei — Wed, 26 Nov 2014 15:22:16 GMT

Thanks for Quick Answer Massimo.

Unfortunately, Peek as previous only returns the value for the rows that previous row has value.

By using peek I could inform the row parameter that could find n rows above a valid value.

However, I couldn't find a way to specify this parameter.

Do you have any idea ?

Re: Repeat Values Loading

Wed, 26 Nov 2014 15:37:45 GMT

This code will work as long as you have the input data in the right order:

Table:

load * inline
[DateID, Rate
10, 0.15
11,
12, 0.17
13,
14,
15, 0.38];

Table2:
load
DateID,
Rate,
if(len(trim(Rate))>0, Rate, peek(NewRate)) as NewRate
Resident Table;

drop table Table;

Re: Repeat Values Loading

vinicius_siquei — Wed, 26 Nov 2014 15:51:11 GMT

I was doing something wrong Massimo. Thanks.

Re: Repeat Values Loading

maxgro — Wed, 26 Nov 2014 15:52:35 GMT

I get this with peek, isn'it the expected result?

Re: Repeat Values Loading

vinicius_siquei — Wed, 26 Nov 2014 16:04:11 GMT

You're right, I was doing something wrong.