Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

markus_riebelin
New Contributor

How to use conditional peek/previous

I'm sorry if this has been asked before, but I couldn't find anything on it.  

Lets say I have the following data:

Some_Data:

Load * Inline [

Id, Group, Value

1, A, 10

2, B, 10

3, C, 20

4, B, 15

5, A , 30

];

I would like to Load this data and add a column (foo) that adds the previous row's value of the last identical group.

So the desired loaded table would look like:

IDGroupValuefoo
1A1010
2B1010
3C2020
4B1525 // ID4+ID2
5A3040 // ID5+ID1

If I use 'Previous(Value) + Value as foo' it will not consider the Group column. With Peek() I need to specify the row index.

Maybe I can iterate throw the Data-set to find the last record with the same 'Group', and use the index for Peek(), but I don't know how to in Qlik.

Can someone help me?

1 Solution

Accepted Solutions
markus_riebelin
New Contributor

Re: How to use conditional peek/previous

Thanks a lot. I somehow didn't even think of using the order to solve my problem.

However it is important to preserve the order by Id when adding the Value of Groups. Also (and I didn't make it clear enough in my example) I want to add onto the Last added Values, when the next row with the same Group is found.

So based on your reply (thanks again), the correct answer would be:

Data:

Load * Inline [

Id, Group, Value

  1, A, 10

  2, B, 10

  3, C, 20

  4, B, 15

  5, A , 30

  6, B, 3

  7, A, 7

];


NoConcatenate

NewData:

LOAD *,

IF(Group = PEEK('Group'),Value + PEEK('NewField'),Value) AS  NewField

RESIDENT Data

ORDER BY Group,Id ASC;

Drop Table Data;

3 Replies
Gabriel
Valued Contributor II

Re: How to use conditional peek/previous

Hi,

Try this script below

ome_Data:

Load * Inline [

Id, Group, Value

1, A, 10

2, B, 10

3, C, 20

4, B, 15

5, A , 30

];

NOCONCATENATE

LOAD *,

IF(Group = PEEK('Group'),Value + PEEK('Value'),Value) AS  NewField

RESIDENT Some_Data

ORDER BY Group,Value ASC

;

DROP TABLE Some_Data;

Hopefully it helps

markus_riebelin
New Contributor

Re: How to use conditional peek/previous

Thanks a lot. I somehow didn't even think of using the order to solve my problem.

However it is important to preserve the order by Id when adding the Value of Groups. Also (and I didn't make it clear enough in my example) I want to add onto the Last added Values, when the next row with the same Group is found.

So based on your reply (thanks again), the correct answer would be:

Data:

Load * Inline [

Id, Group, Value

  1, A, 10

  2, B, 10

  3, C, 20

  4, B, 15

  5, A , 30

  6, B, 3

  7, A, 7

];


NoConcatenate

NewData:

LOAD *,

IF(Group = PEEK('Group'),Value + PEEK('NewField'),Value) AS  NewField

RESIDENT Data

ORDER BY Group,Id ASC;

Drop Table Data;

Gabriel
Valued Contributor II

Re: How to use conditional peek/previous

Please mark it as correct so your question can be closed