kdb+

How to compare multiple columns and update a new column with boolean logic in kdb/q?


Compare the numbers in 3 columns and then create a new column based on the result of the boolean check.

id    col1     col2       col3         col4                    
---------------------------------------------

update checkCol:$[(exec col1 from table)~(exec col2 from table1)~(exec col3 from table2);1;0] from table

I know the code above won't work but it gives a good idea of what I want to achieve. Is there a better way to approach this other than just a standard update statement?

q)tb:([] id:1 2 3 4; num1: 20 22 24 26; num2: 21 22 24 27; num3: 20 22 24 28)
q)tb
id num1 num2 num3
-----------------
1  20   21   20  
2  22   22   22  
3  24   24   24  
4  26   27   28  
q)n1:exec num1 from tb
q)n2:exec num2 from tb
q)n3:exec num3 from tb
q){$[y~x;1;0]}'[n1;n2]
0 1 1 0

Adding the 3rd col causes issues


Solution

  • q)update checkCol:1={count distinct x}each flip (num1;num2;num3) from tb
    id num1 num2 num3 checkCol
    --------------------------
    1  20   21   20   0
    2  22   22   22   1
    3  24   24   24   1
    4  26   27   28   0
    

    If you just want booleans output of the table:

    q)1={count distinct x}each flip tb`num1`num2`num3
    0110b