When all columns (d1;d4) = "NC" for each pair I am looking to update adjustment to "NO ADJ"
In the below case only the first row satisfies the condition and should be updated
t:flip (`pair`d1`d2`d3`d4`vol`adjustment)!(`pair1`pair2`pair3`pair4;("NC";"3/-0.09";"1/-0.09";"NC");("NC";"4/-0.09";"-1/-0.09";"NC");("NC";"2/-0.09";"1/0.09";"2/0.3");("NC";"4/-0.09";"0/-0.09";"NC");0 89.68 78.3 0;("0.1bp";"0.1bp";"0.1bp";"0.1bp"))
Thanks in advance!
While Eliot's answer is perfectly correct for the question at face value. If you have many columns of name like "d*" then you can generalise to this form and it will check all columns (d1,d2,...,dn) for "NC".
![`t;{(like;x;"NC")}each ((cols t) where (cols t) like "d*");0b;(enlist `adjustment)!enlist (enlist;"NO ADJ")]
What this update does is take each column like "d*" and if all columns are "NC" it will change adjustment to "NO ADJ" as requested.
This update statement can be modified as necessary for different column groups.
Edit: As Dunny's comment suggests, having `t at 
This update statement will do what you need:
update adjustment:enlist"NO ADJ" from t where all(d1;d2;d3;d4)~\:\:"NC"
Two each-lefts are needed since (d1;...;d4) is a list of list of strings. The result of this comparison is
q)(t`d1;t`d2;t`d3;t`d4)~\:\:"NC"
1001b
1001b
1000b
1001b
then applying the all keyword will "squash" these booleans into one list:
q)all(t`d1;t`d2;t`d3;t`d4)~\:\:"NC"
1000b
Finally, you need to use enlist on the string "NO ADJ", otherwise there will be a length error due to kdb trying to update the adjustement column pairwise for each character in the string "NO ADJ".
This should provide the solution for the data you provided. You need to use each left to apply the where clause across all columns. This will leave you with a list of 4 booleans of where the condition is met within each row. This is why we then use the all keyword aswell to ensure we only update the result which matches every column.
q)update adjustment:enlist"NO ADJ" from t where all (d1;d2;d3;d4) like\:"NC"
pair d1 d2 d3 d4 vol adjustment
---------------------------------------------------------------
pair1 "NC" "NC" "NC" "NC" 0 "NO ADJ"
pair2 "3/-0.09" "4/-0.09" "2/-0.09" "4/-0.09" 89.68 "0.1bp"
pair3 "1/-0.09" "-1/-0.09" "1/0.09" "0/-0.09" 78.3 "0.1bp"
pair4 "NC" "NC" "2/0.3" "NC" 0 "0.1bp"
One way of doing this
q)t
pair d1 d2 d3 d4 vol adjustment
---------------------------------------------------------------
pair1 "NC" "NC" "NC" "NC" 0 "0.1bp"
pair2 "3/-0.09" "4/-0.09" "2/-0.09" "4/-0.09" 89.68 "0.1bp"
pair3 "1/-0.09" "-1/-0.09" "1/0.09" "0/-0.09" 78.3 "0.1bp"
pair4 "NC" "NC" "2/0.3" "NC" 0 "0.1bp"
q)update adjustment:enlist"NO ADJ" from t where([]d1;d2;d3;d4)~\:`d1`d2`d3`d4!4#enlist"NC"
pair d1 d2 d3 d4 vol adjustment
---------------------------------------------------------------
pair1 "NC" "NC" "NC" "NC" 0 "NO ADJ"
pair2 "3/-0.09" "4/-0.09" "2/-0.09" "4/-0.09" 89.68 "0.1bp"
pair3 "1/-0.09" "-1/-0.09" "1/0.09" "0/-0.09" 78.3 "0.1bp"
pair4 "NC" "NC" "2/0.3" "NC" 0 "0.1bp"
This works by first creating an intermediary dictionary
q)`d1`d2`d3`d4!4#enlist"NC"
d1| "NC"
d2| "NC"
d3| "NC"
d4| "NC"
and then checking is each element of the table ([]d1;d2;d3;d4) exactly equal to this dictionary
e.g similar to constructs like
1 2 3~\:1
In this case we're using the fact that all a table is is a list of dictionaries and, with some lateral thinking, we can often find ways to exploit this fact
So far all of currently suggested solutions fail in the case of multiple replacements
q)t:100000 # flip (`pair`d1`d2`d3`d4`vol`adjustment)!(`pair1`pair2`pair3`pair4;("NC";"3/-0.09";"1/-0.09";"NC");("NC";"4/-0.09";"-1/-0.09";"NC");("NC";"2/-0.09";"1/0.09";"2/0.3");("NC";"4/-0.09";"0/-0.09";"NC");0 89.68 78.3 0;("0.1bp";"0.1bp";"0.1bp";"0.1bp"))
q)update adjustment:enlist"NO ADJ" from t where all(d1;d2;d3;d4)~\:\:"NC"
'length
[0] update adjustment:enlist"NO ADJ" from t where all(d1;d2;d3;d4)~\:\:"NC"
^
q)update adjustment:enlist"NO ADJ" from t where all (d1;d2;d3;d4) like\:"NC"
'length
[0] update adjustment:enlist"NO ADJ" from t where all (d1;d2;d3;d4) like\:"NC"
q)![`t;{(like;x;"NC")}each ((cols t) where (cols t) like "d*");0b;(enlist `adjustment)!enlist (enlist;"NO ADJ")]
'length
[0] ![`t;{(like;x;"NC")}each ((cols t) where (cols t) like "d*");0b;(enlist `adjustment)!enlist (enlist;"NO ADJ")]
^
This is because there is no easy way to determine the number of "NO ADJ" strings you need to substitute in.
'length
[0] update adjustment:enlist "adfjkl" from t where i in 1 2 3
^
q)update adjustment:3#enlist "adfjkl" from t where i in 1 2 3
pair d1 d2 d3 d4 vol adjustment
---------------------------------------------------------------
pair1 "NC" "NC" "NC" "NC" 0 "0.1bp"
pair2 "3/-0.09" "4/-0.09" "2/-0.09" "4/-0.09" 89.68 "adfjkl"
pair3 "1/-0.09" "-1/-0.09" "1/0.09" "0/-0.09" 78.3 "adfjkl"
pair4 "NC" "NC" "2/0.3" "NC" 0 "adfjkl"
pair1 "NC" "NC" "NC" "NC" 0 "0.1bp"
The best way to handle this type of vector replacment is through a vector conditional
q)update adjustment:?[&/[{"NC" ~/:x} each (d1;d2;d3;d4)];(count adjustment)#enlist "NO ADJ";adjustment] from t
pair d1 d2 d3 d4 vol adjustment
---------------------------------------------------------------
pair1 "NC" "NC" "NC" "NC" 0 "NO ADJ"
pair2 "3/-0.09" "4/-0.09" "2/-0.09" "4/-0.09" 89.68 "0.1bp"
pair3 "1/-0.09" "-1/-0.09" "1/0.09" "0/-0.09" 78.3 "0.1bp"
pair4 "NC" "NC" "2/0.3" "NC" 0 "0.1bp"
pair1 "NC" "NC" "NC" "NC" 0 "NO ADJ"
The performance of this will also generally be excellent.
Examining my conditional
?[&/[{"NC" ~/:x} each (d1;d2;d3;d4)];(count adjustment)#enlist "NO ADJ";adjustment]
In my where clause, {"NC" ~/:x} each (d1;d2;d3;d4) produces 4 vector boolean lists, I then collapse them with & (and) and the converge operation, to get where all conditions are true.
The other two components are my replacement vectors, they must be of equal length. This condition avoids the pitfalls of the other two attempts where you cannot know the number of replacements required.
