Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Peek function for more than one dimension

Hi,

I am trying to create values for every number in my table using the peek function. It's working with only one dimension but not with two. This is my input:

LOAD * INLINE [

    No, Item, Value

    1, A, 100

    1, B, 50

    4, A, 200

    4, B, 300

    5, A, 99

    5, B, 400  

];

and I want the output to look like this:

    

NoItemAB
1 10050
2 10050
3 10050
4 200300
5 99400
6 99400

I have tried with the following script:

NumberTable:

Load RecNo() as No

AutoGenerate (6);

Left join

LOAD * INLINE [

    No, Item, Value

    1, A, 100

    1, B, 50

    4, A, 200

    4, B, 300

    5, A, 99

    5, B, 400  

];

//NewTable:

NoConcatenate

Load No,

     if(IsNull(Item),Peek(Item),Item) as Item,

     if(IsNull(Value),Peek(Value),Value) as Value

Resident NumberTable

Order by No, Item Asc;

Drop Table NumberTable;

But the peek function is only working for Item B:

    

NoItemAB
1 10050
2 -50
3 -50
4 200300
5 99400
6 -400
1 Reply
sunny_talwar

Try this:

NumberTable:

Load RecNo() as No

AutoGenerate (6);

Left Join (NumberTable)

LOAD * Inline [

Item

A

B

];

Left join

LOAD * INLINE [

    No, Item, Value

    1, A, 100

    1, B, 50

    4, A, 200

    4, B, 300

    5, A, 99

    5, B, 400 

];

NewTable:

NoConcatenate

Load No,

     Item,

     if(IsNull(Value),Peek(Value),Value) as Value

Resident NumberTable

Order by Item, No;

DROP Table NumberTable;