0

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?

Thomas Smyth - Treliant
  • 4,993
  • 6
  • 25
  • 36
duckman
  • 687
  • 1
  • 15
  • 30

2 Answers2

5

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.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Dan Sheppard
  • 106
  • 2
4

To provide further explanation as to why your approaches were not working

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

(var1+var2)>0 includes both var1 and var2 being equal to 1, resulting in the last Price being divided by 2, when this is not the intention

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

q evaluates from right to left (also called Left-of-Right evaluation), meaning your code is actually executing this (once implicit brackets are added in)

var1=1 and var2=0 => (var1=(1 and (var2=0)))
var1=0 and var2=1 => (var1=(0 and (var2=1)))

The 1 and will always leave the right hand side unchanged and the 0 and on the second row will always return 0, so the whole line simplifies to:

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

Which is different to the condition you are trying to code. To fix this, you can add in extra brackets to ensure this evaluates as you expect it to, although Dan's answer is more efficient

varNew:?[((var1=1) and var2=0)|((var1=0) and var2=1);Price%2;Price] from table
Mark Kelly
  • 1,780
  • 7
  • 16