kdb: Add a column showing sum of rows in a table with dynamic headers while ignoring nulls

Question

I have a table whose columns are dynamic, except one column:A. The table also has some null values (0n) in it. How do I add another column that shows total of each row and either ignores the column that has "0n" in that particular row or takes 0 in its place. Here is my code, it fails on sum and also does not ignore nulls.

addTotalCol:{[]
    table:flip`A`B`C`D!4 4#til 9;
    colsToSum: string (cols table) except `A;   / don't sum A
    table: update Total: sum (colsToSum) from table;  / type error here. Also check for nulls
    :table;
   } 

Answer 1

I think it is better to use functional update in your case:

addTotalCol:{[]
    table:flip`A`B`C`D!4 4#til 9;
    colsToSum:cols[table] except `A;   / don't sum A
    table:![table;();0b;enlist[`Total]!enlist(sum;enlist,colsToSum)];
    :table;
   }

Reason why it is not working is because your fourth line is parsed as:

table: update Total: sum (enlist"B";enlist"C";enlist"D") from table;

Since sum only works with numbers, it returns 'type error since your inputs are string.

Another solution to use colsToSum as string input:

addTotalCol:{[]
    table:flip`A`B`C`D!4 4#til 9;
    colsToSum:string cols[table] except `A;   / don't sum A
    table:get"update Total:sum(",sv[";";colsToSum],") from table"
    :table;
   }

Basically this will build the query in string before it is executed in q.

Still, functional update is preferred though.

EDIT: Full answer to sum 0n:

addTotalCol:{[]
    table:flip`A`B`C`D!4 4#0n,til 9;
    colsToSum:cols[table] except `A;   / don't sum A
    table:![table;();0b;enlist[`Total]!enlist(sum;(^;0;enlist,colsToSum))];
    :table;
   }