Peek and Previous

[niranjana]

Hi, i have a table: 

month , Sales

Jan,  43

Feb, 32

March , 

April, 30

May, 

I need to bring previous values: i.e. , Sale must contain 

43,32,32,30,30

I used the code:

load Month, 

if(isnull(Sales), previous (Sales), Sales)) 

from table ;

My question is if this code is crct or Peek function has to be used...

Thanks in advance

Niranjana

[Oleg_Troyansky]

Hi Niranjana,

Yes, this is correct, assuming that the data is sorted properly.

cheers,

[niranjana]

hi thanks for your reply.. I dont know why but isnull didnt seem to work....i used len(Sales)=0 instead and it worked...can you explain why?

Also, can we use peek in this case?

[MarcoWedel]

what result would you expect for two consecutive missing sales values?

[Oleg_Troyansky · Accepted solution]

Hi Niranjana,

Regarding the isnull() function - I also find it a bit flakey. In the past, it was known to give different results in 32-bit and 64-bit environments. These differences are probably behind us, but ever since I stopped using isnull(). Like you, I'm using len(trim(filed)) instead. This way, NULL, or a space, or an empty cell in Excel will all return 0 as a length.

You can use Peek, and in this case, considering Marco's question about two consecutive records with null values, it might be even better to use Peek. The subtle difference between Peek() and Previous() is that Peek() is fetching the last loaded row in the Qlik table, while Previous() is fetching the value from the previous row in the source table. 

So, if the source table should have two consecutive NULL values, then the Previous() function will work for the first missing value, but then still return NULL for the second missing value. Pick, on the other hand, should pick up the corrected value from the first corrected row.

Keep in mind that unlike Previous(), the syntax of the Pick() function requires that the filed name is enclosed in single quotes:

Peek('Sales')

Cheers,

[niranjana]

Hi Marco, 

there are NOT consecutive missing values.... but the input is  32,12,34, a  ,b 

then the output should be 32,12, 34,34,34   ...Thanks....

[niranjana]

Hi Oleg,

Thanks for you explanation, but I just want to know what do you mean by sorting ? 

"Yes, this is correct, assuming that the data is sorted properly."

Can you pls write the code in 2 lines?

Also, Do you mean , PEEK will give last non-null loaded row... but previous may give NULL value?

[Oleg_Troyansky · Accepted solution]

What I mean by sorting is that the data in the table needs to be arranged in the desired order. In your example, the data should be sorted by Month, and not alphabetically but numerically (assuming that your Month is a dual date field) - something like this:

load Month, 

if(isnull(Sales), previous (Sales), Sales)) 

from table

ORDER BY Month

;

On your second question - if two or more lines should contain NULL values, then you will see the different on the second line with the missing value - PEEK pill populate the previously loaded value, and Previous will populate the value from the previous source row, which is NULL in this case. I'll illustrate it here:

Month   Sales     Previous     Peek

Jan          30                 -                  -

Feb         NULL          30             30

Mar         NULL        NULL         30

Do you see the difference?