user21142340
user21142340

Reputation:

KDB+ Q Create a looping/iteration id column

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

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?

Upvotes: 0

Views: 223

Answers (1)

rianoc
rianoc

Reputation: 3841

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.

Upvotes: 1

Related Questions