How can I apply a function with 'if' to a table?

Community Support
,
1

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:AAPLAAPLGOOGLGOOGL;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 t1

 

However, it gave error message:

'type

  [4]  f:{[a;b] if[(a+b)=0;0;a*b]}

                        ^

 

How can I solve this problem?

I attach a photo of the whole process.

2

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 0

Some documentation on $ also here: https://code.kx.com/q/ref/cond/

3

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:AAPLAAPLGOOGLGOOGL;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.

 

4

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

 

5

You can also forgo any conditional statements in this case:

 

q)update r:p1p2not 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