KDB: Function optimization

Question

I have 2 table in kdb as below

q)table1:([]A:1 2 3 5 5 6 2 1;B:`HAK`ZAK`NAK`AAK`AZK`HAK`ZAK`HAK;C:2000.01.01+0 1 2 3 4 0 1 0)
q)table1
A B   C
----------------
1 HAK 2000.01.01
2 ZAK 2000.01.02
3 NAK 2000.01.03
5 AAK 2000.01.04
5 AZK 2000.01.05
6 HAK 2000.01.01
2 ZAK 2000.01.02
1 HAK 2000.01.01
q)table2:([]B:`HAK`ZAK`NAK`AAK`AZK;Z:`NAFK`RFK`NAFK`RFK`ORQ)
q)table2
B   Z
--------
HAK NAFK
ZAK RFK
NAK NAFK
AAK RFK
AZK ORQ

I want to modify Table1 column B as per mapping of Table 2. eg wherever in table1 columnB has word "HAK", then look in table2 columnB and replace table1 with respective table2 columnz Same for all rows for table1.

final output i want is table1 should be updated like below.

A B    C
-----------------
1 NAFK 2000.01.01
2 RFK  2000.01.02
3 NAFK 2000.01.03
5 RFK  2000.01.04
5 ORQ  2000.01.05
6 NAFK 2000.01.01
2 RFK  2000.01.02
1 NAFK 2000.01.01

The function which I came up with is below.

hfun:
{$[
x in `$("HAK");`$("NAFK");
x in `$("ZAK");`$("RFK");
x in `$("NAK");`$("NAFK");
x in `$("AAK");`$("RFK");
x in `$(AZK);`$("ORQ");
x]}


finalOutput:update B:hfun'[B] from table1

The above function is working as expected but its not feasible to write every time a new function for new mappings or if table2 has 200 rows.

Can someone please take a look and advise further?

Eliot Robinson · Accepted Answer · 2020-01-16 09:01:02Z

4

Could also use an amend to achieve this:

@[table1;`B;(!/)table2`B`Z]

answered Jan 16, 2020 at 9:01

Eliot Robinson

6663 silver badges8 bronze badges

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

Comments

Phil Gunning · Accepted Answer · 2020-01-16 08:58:35Z

3

update B:({x[;0]!x[;1]}flip value flip table2)'[B]from table1

This will achieve the desired outcome without the need to define extra variables or conditional statements also. It also works with spaces in symbols.

answered Jan 16, 2020 at 8:58

Phil Gunning

2221 silver badge10 bronze badges

Comments

Connor Gervin · Accepted Answer · 2020-01-16 08:53:09Z

2

You can use a dictionary update instead of the loop w/ condition:

dict:`HAK`ZAK`NAK`AAK`AZK!`NAFK`RFK`NAFK`RFK`ORQ;
update B^dict B from table1

(with spaces)
table1:([]A:1 2 3 5 5 6 2 1;B:(`$"HAK z";`$"ZAK";`$"NAK";`$"AAK";`$"AZK";`$"HAK";`$"ZAK";`$"HAK");C:2000.01.01+0 1 2 3 4 0 1 0)
table2:([]B:(`$"HAK z";`$"ZAK";`$"NAK";`$"AAK";`$"AZK");Z:`NAFK`RFK`NAFK`RFK`ORQ)

dict:exec B!Z from table2;
update B^dict B from table1

edited Jan 16, 2020 at 8:53

answered Jan 16, 2020 at 8:29

Connor Gervin

9465 silver badges17 bronze badges

4 Comments

BlackPearl Over a year ago

thanks connor, what if table1 columnB has space in between words. eg HAK 1 HAK 2 `HAK 3 Is there way we can achive this? I have 80-200 records in table2. Also can we create a dictionary directly using table2?

Jonathon McMurray Over a year ago

@Haiderali Connor's second example of generating dict using the exec statement will work regardless of spaces etc.

DanDan4561 Over a year ago

Nice wan Connor

BlackPearl Over a year ago

Awesome It worked, thanks a lot Connor, really appriciate your quick respose.

charley · Accepted Answer · 2020-01-17 09:37:24Z

0

You can also use an lj

q)select A,B:B^Z,C from table1 lj `B xkey table2
A B    C
-----------------
1 NAFK 2000.01.01
2 RFK  2000.01.02
3 NAFK 2000.01.03
5 RFK  2000.01.04
5 ORQ  2000.01.05
6 NAFK 2000.01.01
2 RFK  2000.01.02
1 NAFK 2000.01.01

answered Jan 17, 2020 at 9:37

charley

911 bronze badge

Collectives™ on Stack Overflow

KDB: Function optimization

4 Answers 4

Comments

Comments

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related