How to use conditional peek/previous

[Anonymous]

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:

ID	Group	Value	foo
1	A	10	10
2	B	10	10
3	C	20	20
4	B	15	25 // ID4+ID2
5	A	30	40 // 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?

[Gabriel]

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

[Anonymous · Accepted solution]

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]

Please mark it as correct so your question can be closed