KDB+ Q Create a looping/iteration id column

Question

I have a kdb/q question I would like some help with. Please see the table below

`A  B   Run
99  newcol  Start
99  newcol  Start
99  newcol  Start
99  newcol  During
99  newcol  During
99  newcol  End
99  newcol  End
99  newcol  End
99  newcol  Start
99  newcol  During
99  newcol  End
80  newcol2 Start
80  newcol2 End
80  newcol  End`

In the table above there is 3 columns A,B,Run I would ideally like to create a run id column

• col A is a unique identifier
• col b is not unique

What I would like to create below

`A  B        Run    Run-ID
99  newcol  Start   99-1
99  newcol  Start   99-1
99  newcol  Start   99-1
99  newcol  During  99-1
99  newcol  During  99-1
99  newcol  End     99-1
99  newcol  End     99-1
99  newcol  End     99-1
99  newcol  Start   99-2
99  newcol  During  99-2
99  newcol  End     99-2
80  newcol2 Start   80-1
80  newcol2 End     80-1
80  newcol  End     80-1`

Would it be possibly to iterate/loop this kind of logic in q? any help is grateful thankyou!

How to id a complete run to create an id?

• A run begins with the word Start
• A run ends with the word End
• If a run has consecutive starts at the beginning and col A is the same then this is the same run and the id should stay the same until the run ends
• A new id should only be generated after and end and the next row is a start ie if there is consecutive ends the id should keep looping until there is a new start

Answer 1

Provide the sample in a format more easily added to a kdb+ table if you can. Makes it easier for responders to help you:

tab:flip `A`B`Run!flip (
 (99  ;`newcol  ;`Start);
 (99  ;`newcol  ;`Start);
 (99  ;`newcol  ;`Start);
 (99  ;`newcol  ;`During);
 (99  ;`newcol  ;`During);
 (99  ;`newcol  ;`End);
 (99  ;`newcol  ;`End);
 (99  ;`newcol  ;`End);
 (99  ;`newcol  ;`Start);
 (99  ;`newcol  ;`During);
 (99  ;`newcol  ;`End);
 (80  ;`newcol2 ;`Start);
 (80  ;`newcol2 ;`End);
 (80  ;`newcol  ;`End))

One way to perform what you want:

update RunID:{`$x,"-",y}'[string[A];string sums 1=deltas Run=`Start] by A from tab


A  B       Run    RunID
-----------------------
99 newcol  Start  99-1 
99 newcol  Start  99-1 
99 newcol  Start  99-1 
99 newcol  During 99-1 
99 newcol  During 99-1 
99 newcol  End    99-1 
99 newcol  End    99-1 
99 newcol  End    99-1 
99 newcol  Start  99-2 
99 newcol  During 99-2 
99 newcol  End    99-2 
80 newcol2 Start  80-1 
80 newcol2 End    80-1 
80 newcol  End    80-1

Note I named the column RunID not Run-ID as using - in column names is not recommended. - is used for minus and causes errors if you try to select a column with it in it's name.