KX Community

Find answers, ask questions, and connect with our KX Community around the world.
KX Community Guidelines

Home Forums kdb+ How can I apply a function with ‘if’ to a table?

  • How can I apply a function with ‘if’ to a table?

    Posted by voiddump on February 1, 2024 at 12:00 am

    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 t1

     

    However, it gave error message:

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

                            ^

     

    How can I solve this problem?

     

    voiddump replied 3 months, 1 week ago 5 Members · 4 Replies
  • 4 Replies
  • amcnaught921

    Member
    February 1, 2024 at 12:00 am

    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/

  • acapper

    Member
    February 1, 2024 at 12:00 am

    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.

     

  • cillianreilly

    Member
    February 1, 2024 at 12:00 am

    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

     

     

     

  • Ciaran

    Member
    February 1, 2024 at 12:00 am

    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.