Set values for all records in a Kdb table based on values of other columns

Question

I need to perform a global update on a KDB table to update two columns. For the FirstName column, I want to remove it's value for records which have empty string in the SecondName column, and for the FullName column I want to replace an encoded delimiter with a space for all rows in the table.

These need not be done in a single update statement if that helps.

update
    FirstName:$[SecondName like ""; FirstName; ""],
    FullName[FullName; "&nbsp;"; " "]
    from table
    }

I'm struggling with the syntax - the above is my best attempt but it doesn't work.

If it helps, think of references to column names like vectors, not scalars... — pamphlet
– pamphlet, Commented Jul 23, 2019 at 15:05

Rahul · Accepted Answer · 2019-07-23 15:03:52Z

2

One way to achieve that in a sinlge update statement:

q) update FirstName:?[SecondName like ""; SecondName;FirstName], FullName:ssr[;"&nbsp;"; " "]@'FullName from table

answered Jul 23, 2019 at 15:03

Rahul

3,9791 gold badge16 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

wwarby Over a year ago

Blimey - that was quick :) I only refreshed the page 10 minutes ago and didn't refresh again before posting my own solution - yours is definitely nicer than mine though, thanks!

Rahul Over a year ago

Glad it helped.

Sander Rõõmus · Accepted Answer · 2019-07-23 15:11:32Z

2

For your update for the FirstName you need a ? rather than a $ as the Execution control operator. As it does the execution with a list rather than an atom. For the FullName you will need to use ssr, which finds where string has "&nbsp" and replaces it with " "

Which would give the following:

q)tab:([]FirstName:("aa";"cc");SecondName:("";"dd");FullName:("aa&nbsp;";"cc&nbsp;dd"))
q)update FirstName:?[SecondName like ""; count[FirstName]#enlist""; FirstName],FullName:ssr[; "&nbsp;";" "]each FullName from tab
FirstName SecondName FullName
-----------------------------
""        ""         "aa "
"cc"      "dd"       "cc dd"

Hope this answers your question.

Regards, Sander

answered Jul 23, 2019 at 15:11

Sander Rõõmus

1354 bronze badges

1 Comment

wwarby Over a year ago

Thanks very much Sander - seems there are many different ways to achieve this result. I have something that works now so won't waste time trying every solution offered but I reallly appreciate your input :)

Anton Dovzhenko · Accepted Answer · 2019-07-23 15:06:14Z

1

I would recommend to do it in two steps

//create table with mock data
table: ([]FirstName: ("aaa";"ccc"); SecondName: ("bbb";""); FullName: ("aaa bbb";"ccc "));
//step1: set First to "" whenever SecondName is ""
table: update FirstName: (count i)#enlist"" from table where SecondName like "";
//step2: replace spaces in FullName
table: update FullName: ssr[;"&nbsp;";" "] each FullName from table;

answered Jul 23, 2019 at 15:06

Anton Dovzhenko

2,57913 silver badges16 bronze badges

1 Comment

wwarby Over a year ago

Hah - very similar to mine - amazed at how many responses I got so quickly - thanks a lot :)

wwarby · Accepted Answer · 2019-07-23 15:12:01Z

1

Got it I think:

table:update FirstName:(count i)#enlist "" from table where SecondName like "";
table:update FullName:{ ssr[x; "&nbsp;"; " "] } each FullName from table where FullName like "*&nbsp;*";

answered Jul 23, 2019 at 15:12

wwarby

2,0811 gold badge27 silver badges41 bronze badges

Collectives™ on Stack Overflow

Set values for all records in a Kdb table based on values of other columns

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related