Search code examples
kdb

KDB/Q: create a new column based on the value of other columns

This might be a simple question but I cannot get it to work. I am trying to create a column varNew that equals half of a column Price if only one of the columns var1 and var2 has a value of 1. var1 and var2 only takes a value of 0 or 1. If both var1 and var2 are 1 then varNew = Price. so the wanted data looks like this

var1    var2    Price   varNew
0       0       10      10
0       1       14      7
1       0       12      6
1       1       20      20

I tried:

update varNew:?[((var1+var2)>0);Price%2;Price] from table and

varNew:?[(var1=1 and var2=0)|(var1=0 and var2=1);Price%2;Price] from table and

varNew:?[var1=1 or var2=1;Price%2;Price] from table

but they don not work as expected. particularly, it returns 12 instead of 6. What is going on? why these conditions dont work? and how should I do it?

Solution

  • A solution is:

     q)t:([]var1:0011b;var2:0101b;Price:10 14 12 20)
 q)update varNew:?[var1<>var2;Price%2;Price] from t

    This is one of the many overloads of the ? operator (https://code.kx.com/q/ref/overloads/#query). In this form it is used as a vector conditional.