Missing Manual - Peek()

[robert_mika]

         

    Have you ever wondered how the examples from the Qlikview help may look like?

Please see below and enjoy responsibly...

Peek()

not a boo...

This function belongs to to Inter-Record functions and can only be used in the script (back-end).

Description(Qlikview Help)

Returns the contents of the fieldname in the record specified by row in the internal table tablename. Data are fetched from the associative QlikView database.

Fieldname must be given as a string (e.g. a quoted literal).

Row must be an integer. 0 denotes the first record, 1 the second and so on. Negative numbers indicate order from the end of the table. -1 denotes the last record read.

If no row is stated, -1 is assumed.

Tablename is a table label  without the ending colon. If no tablename is stated, the current table is assumed. If used outside the load statement or referring to another table, the tablename must be included.

Syntax:

Field_name -  is a name of of your field(column)

row_no - the row from which the data is returned

(0 is first row

1 is second row

and so on..

-1 is the last row)

table_name - a name of table from where the data are fetched

Returns values from previous row or row specified by the row-no argument.

• If no row is stated, -1 is assumed.
• you can reference field that was not been previously loaded (created on the with AS alias)(See "Bonus 2" example)
• From the Help description "Fieldname must be given as a string (e.g. a quoted literal)."

          This is true only if we use this function to create variable (please see below examples)

Data Model
(Copy and Pasted below code into Edit Script window and reload)

Tab1:

load

peek(Sales) as S1,

peek( Sales,2 ) as S2,

peek( Sales,-2 ) as S3,

if(rowno()=1,Sales,Peek(RunnigTotal,-1)+Sales) as RunnigTotal,

Previous(Sales) as PSales,

numsum( Sales, peek( 'Sales' ) ) as Bsum,

Sales

inline [

Sales

100

200

300

400

]

;

load

peek( Sales, 0, 'Tab1' )as S4

resident Tab1;

LET vpeek= peek( 'Sales', -1, 'Tab1' ) ;

Please see below swuehl comments about difference when you use negative or positive numbers as second argument.

Is worth to mention that Peek() is reading from Output table(opposite to Previous() function which reads from Input Table.

Example:

OutputTable:

LOAD field

RESIDENT InputTable;

source:Difference between peek() and previous() funcation‌‌‌

Example 1

In this example as we did not specify the row_no argument, all but last rows are returned.

                                  peek( 'Sales' )

                                                  peek( 'Sales' ) = peek( 'Sales',-1)

Example 2

IF the row_no argument  is added only ONE value is returned

                        peek( 'Sales',2 )  Remember 2 = third row

                       

Example 3

If we use negatives numbers as row_no  the return value is our initial table minus number of rows specified by the second argument.

                              peek( 'Sales',-2 )

As you see the last 2 rows have been removed from the table.

Example 4

The below syntax is used when we want to return value from external table(see data model)

peek( 'Sales', 0, 'Tab1' ) as S4

Example 5

Each row is a SUM of current row + one row above.

numsum( Sales, peek( 'Sales' ) ) as Bsum

Bonus

How to store value into variable:

With below example

LET vpeek= peek( 'Sales', -1, 'Tab1' ) ;

we will store value of 400  in variable vpeek

and then use in front end development.

Bonus 2

As previously stated we can use Peek to return values from field that was not been yet created.

How does it wok?

In our data model we have this line:

if(rowno()=1,Sales,Peek(RunnigTotal,-1)+Sales) as RunnigTotal,

Although RuningTotal has not yet been loaded we can return the values from that line:

Still feeling Qlikngry?

How To /Missing Manual(17 articles)

[swuehl]

Robert, I like your series, but this time I think we need to clarify at least one misleading point here (especially since you set the statement in bold letters):

a) I believe PEEK() is always returning a value of a single record, never a table

If you use a positive number N as second argument, it will return the the Nth row of the addressed table,

it's an absolute coordinate, counted from the start of your table.

Of course it can only return that row's value when it's existing.

If you use a negative number, it will use the Nth row counted from the end of your adressed table, but if you use peek to address a field within the same table you are loading and using PEEK, the table will grow while loading, thus returning each time a different row.

I think that's what you are seeing in your example.

But PEEK() will always return a single row value, not a table.

[robert_mika]

Hi Stefan,

Appreciate you comments and correction.

My aim was to give some layman's term for new users but  think I have gone too far.

Your explanation is very right and this is what I was going to say.

I have removed the statement in bold.

Is there anything else you would change?

[Anonymous]

Great resource and really like your open collaboration. Bravo.

[robert_mika]

Thank you Sara.

[swuehl]

Hi Robert,

Now since you've linked to my comment in your blog, I should have taken more time in phrasing

I think your example 3 should be reviewed, too, demonstrating Peek() with a negative row indicator.

And Bonus2 could be simplified to:

Rangesum( Peek(RunnigTotal,-1), Sales) as RunnigTotal,

When talking about Peek(), I think a small introduction to the concept of input / output tables would be beneficial (not only for beginners), this still leads to confusion.

Keep on going with your series, I think it's a nice idea / concept!

What about Previous() function next?

Regards,

Stefan

[robert_mika]

Hi Stefan,

I think your example 3 should be reviewed, too, demonstrating Peek() with a negative row indicator.

What would you like to add/change?

There is already negative row indicator.

And Bonus2 could be simplified to:

Rangesum( Peek(RunnigTotal,-1), Sales) as RunnigTotal,

Much more simpler.Thanks

When talking about Peek(), I think a small introduction to the concept of input / output tables would be beneficial (not only for beginners), this still leads to confusion.

I could not get better that John did (see above)

Keep on going with your series, I think it's a nice idea / concept!

Thanks.

What about Previous() function next?

On its way

[swuehl]

In example 3, you are still mentioning that the return value is the initial table minus some rows.

I think that's still misleading.

If you use Peek() in a load script, each execution of Peek() will return a single row value of a specific field.

If you look at your table, the numbers of rows in the table has not changed. It's just that the first two calls of Peek('Field', -2) have returned NULL, because there weren't any rows that could be accessed at this point.

So I think there is (at least for me) some mixup between the function call Peek() and the output table that is resulting from the LOAD.

Instead of list boxes, I would prefer to show the resident tables here either as screenshot from the table view, or just manually created. Or create a record number field using recno() and create table boxes using record number and the fields you want to demonstrate.

[Digvijay_Singh]

Hi,

Thanks a lot to Robert and Stefan helping me to uncover these basic concepts as I am still a newcomer.

Now I could understand all values loaded and showing up in preview of table viewer. I was scratching my head why few columns are loaded from 3rd row or 4th row or why they don't start from top. So the key point I could make out is against each 'Sales' value loaded, the rest of the columns are filled up based on how Peek expression is being used.

The only thing I couldn't understand is Peek(Sales,2) i.e. Example 2. Why it is not loaded in 3rd row against sales value of '300'. I could get why nothing will be filled in S2 column  in 1st and 2nd row as by that time (Sales,2) is not loaded but once '300' loaded it should show in 3rd row. Currently it appears on 4th row. Appreciate your help in understanding this.

Thanks,

[swuehl]

Well, I believe that's caused by the subtle difference between the input and output table. PEEK() is addressing the output table, so while the 3rd input record is being processed in the LOAD statement, there actually is no row #3 in the output table.

Only the 4th input record can start addressing the third output table row.

[Anonymous]

Hi Stefan,

If i want to see char values like a, a+b, a+b+c like that how can i show that.