KDB: how to create a sequence number

Question

I would want to create a column whose value would increase if the value of another column changes. Here is an example:

Party   sequence
ABC     1
ABC     1
ABC     1
OOO     2
OOO     2
PIM     3
AAW     4
ABC     5
TOM     6
TOM     6
TOM     6

What I have tried is

update sequence: ?[party=prev party;prev sequence;(prev sequence) + 1]

However, what I keep getting is an error: "Correction hint: length" How can I achieve this task?

score 2 · Accepted Answer · answered Dec 07 '20 at 12:06

2

Differ will give you a boolean for each one that changed and then you can rolling sum the booleans:

q)update sequence:sums differ party from ([]party:`ABC`OOO`PIM`AAW`ABC`TOM where 3 2 1 1 1 3)
party sequence
--------------
ABC   1
ABC   1
ABC   1
OOO   2
OOO   2
PIM   3
AAW   4
ABC   5
TOM   6
TOM   6
TOM   6

answered Dec 07 '20 at 12:06

terrylynch

11,844
13
21

It doesn't - that was just me recreating your sample table precisely. The solution is just `update sequence:sums differ party from yourTable` and it'll work regardless of the contents of party – terrylynch Dec 07 '20 at 12:10
yeah I realised that. sorry it is 11:11 pm here and my brain does not work if at all. one quick question, what if there is another variable, and I want to set the sequence value to 0 when this variable chance. for example , a date variable. so when date changes, sequence is reset to 0 – duckman Dec 07 '20 at 12:13
Ok, no worries! Then you would do it `by date`. So `update sequence:sums differ party by date from yourTable`. – terrylynch Dec 07 '20 at 12:14
it does not work. when I added `by date`, seq remains at 1 – duckman Dec 07 '20 at 12:16
1

What do you mean it remains at one? Check this example: ```update sequence:sums differ party by date from ([]date:2020.01.01 2020.01.02)cross([]party:`ABC`OOO`PIM`AAW`ABC`TOM where 3 2 1 1 1 3)``` the sequence resets on the second date. Is this not what you mean? How is your table different? – terrylynch Dec 07 '20 at 12:24
1

Thanks it works now. I am not sure what I did wrong. I should really go to bed. thank you soooooo much – duckman Dec 07 '20 at 12:55

KDB: how to create a sequence number

1 Answers1