I have a table
t: flip `ref`a`b`c`d`e!(til 10;10?10;10?10;10?10;10?10;10?10)
ref a b c d e
0 5 3 3 9 1
1 1 9 0 0 0
2 5 9 4 1 7
3 0 0 5 1 3
4 2 6 8 9 3
5 3 2 0 6 6
6 7 6 4 9 8
7 4 8 9 7 2
8 7 0 8 8 3
9 7 9 0 4 8
how can I set all values in columns a,b,c ,.. to 0Ni if their value equals the value in column ref without having to do a single line update for every columns?
So something taht would look a bit like (which returns ERROR:type)
{update x:?[x=t;0Ni;x] from t} each `a`b`c`....
The previous answer involves working with strings, this can often get very messy. To avoid this it is possible to build up the query by passing column names as symbols instead. The dictionary for the functional select can be built up using the following function:
q){y!enlist[({?[y=x;0Ni;y]};x)],/:y:(),y}[`ref;`a`b`c]
a| ({?[y=x;0Ni;x]};`ref) `a
b| ({?[y=x;0Ni;x]};`ref) `b
c| ({?[y=x;0Ni;x]};`ref) `c
The initial column is x and it allows to any number of columns to be passed as y for comparison.
This can then be added into the functional select:
q)![t;();0b;{y!enlist[({?[y=x;0Ni;y]};x)],/:y:(),y}[`ref;`a`b`c]]
ref a b c d e
-------------
0 4 5 8 4 8
1 2 6 9 1
2 8 4 7 2 9
3 0 1 2 7 5
4 5 3 0 4
5 8 3 1 6
6 5 7 4 9 6
7 2 8 2 2 1
8 2 7 1 8
9 6 1 8 8 5