-
How can I apply a function with ‘if’ to a table?
Hi! I’m trying to apply a function to a table. The basic idea is that if the 2 prices adds up to 0, then the new column gets 0, otherwise it gets the product of the 2 prices. However I encountered ‘type error.
The codes I wrote were?
//This is for creating t1 t1:([] TimeStamp:2018.01.01 2018.01.01 2018.01.02 2018.01.02;stock:`AAPL`AAPL`GOOGL`GOOGL;p1: 1 2 -2 3;p2: -1 1 2 0) //Then I define a function f:{[a;b] if[(a+b)=0;0;a*b]} //Then I apply t1:update r:f[p1;p2] from t1However, it gave error message:
'type [4] f:{[a;b] if[(a+b)=0;0;a*b]}
^
How can I solve this problem?
-
4 Replies
-
Hey,
The following will give the solution you require
q)t1 TimeStamp stock p1 p2 ---------------------- 2018.01.01 AAPL 1 -1 2018.01.01 AAPL 2 1 2018.01.02 GOOGL -2 2 2018.01.02 GOOGL 3 0 q)f:{$[0=x+y;0;x*y]} q)t1:update r:f'[p1;p2] from t1 TimeStamp stock p1 p2 r ------------------------ 2018.01.01 AAPL 1 -1 0 2018.01.01 AAPL 2 1 2 2018.01.02 GOOGL -2 2 0 2018.01.02 GOOGL 3 0 0Some documentation on $ also here: https://code.kx.com/q/ref/cond/
-
This can be done entirely within the update statement, using a vector conditional.
q)t1:([] TimeStamp:2018.01.01 2018.01.01 2018.01.02 2018.01.02;stock:`AAPL`AAPL`GOOGL`GOOGL;p1: 1 2 -2 3;p2: -1 1 2 0) q)update r:?[(p1+p2)=0;0;p1*p2] from t1 TimeStamp stock p1 p2 r ------------------------ 2018.01.01 AAPL 1 -1 0 2018.01.01 AAPL 2 1 2 2018.01.02 GOOGL -2 2 0 2018.01.02 GOOGL 3 0 0
With further reading found here on vector conditionals.
-
You can also forgo any conditional statements in this case:
q)update r:p1*p2*not p1=neg p2 from t1 TimeStamp stock p1 p2 r ------------------------ 2018.01.01 AAPL 1 -1 0 2018.01.01 AAPL 2 1 2 2018.01.02 GOOGL -2 2 0 2018.01.02 GOOGL 3 0 0
-
Using the vector conditional or applying a function to the columns are both valid solutions that work.
The vector conditional would be the best approach as it is more efficient to use. Due to the fact that it is doing the calculation within the query and doesn’t have to do an each on each value to get the entry for the new column.As shown below using a table of 1 million rows, the difference can be seen for either approach
Log in to reply.