I have a datasource that changed recently (same table however) and I am trying to clean up my table and having an issue with a pesky character " that I am trying to replace with null
When the table is pushed to kdb, it is a symbol column that can have a single double quote (ascii 34.) I have been running ssr to replace it with null and using fills to populate which it had worked at one point before the datasource change. I thought it might be a leading/trailing spaces so I checked with trim which seems to be fine so no rogue spaces are included.
For some reason, I am unable to perform ssr function on it. I’m verifying that the char is correct. I thought I had it working however my update below doesn’t.
Any thoughts? I’m assuming its my regex?
P.S. I was hoping to avoid having to use the 'int $ seg to cast it as ascii but that is my next idea.
// update query does not fail but doesn't update the single, double quote in seg
t: update seg: fills `$ssr[;"\"\"";""] each string seg from t;
//verify the data types are symbols
meta t;
c | t f a
-------------| -----
id | s
seg | s
Here is my attempt at casting the seg as string to see how to escape the quote as well as the desired goal.
id seg Displaystring Ticker ---->> Desired output
------------------------------------------------------
AAA 1 GOOG "GOOG" GOOG
AAA 2 " ,"\"" GOOG
AAA 3 " ,"\"" GOOG
AAA 4 " ,"\"" GOOG
AAA 5 " ,"\"" GOOG
AAA 6 " ,"\"" GOOG
BBB 1 AMZN "AMZN" AMZN
BBB 2 " ,"\"" AMZN
BBB 3 " ,"\"" AMZN
CCC 1 AAPL "AAPL" AAPL
CCC 2 " ,"\"" AAPL
CCC 3 " ,"\"" AAPL
DDD 1 TSLA ,"\"" TSLA
DDD 2 " ,"\"" TSLA
>Solution :
This should do the job
q)tab:([]id:`$raze{x,/:string 1+til y}'[("AAA ";"BBB ";"CCC ";"DDD ");6 3 3 2];seg:@[`$'14#"\"";0 6 9 12;:;`GOOG`AMZN`AAPL`TSLA])
q)tab
id seg
----------
AAA 1 GOOG
AAA 2 "
AAA 3 "
AAA 4 "
AAA 5 "
AAA 6 "
BBB 1 AMZN
BBB 2 "
BBB 3 "
CCC 1 AAPL
CCC 2 "
CCC 3 "
DDD 1 TSLA
DDD 2 "
q)update fills?[seg=`$"\"";`;seg]from tab
id seg
----------
AAA 1 GOOG
AAA 2 GOOG
AAA 3 GOOG
AAA 4 GOOG
AAA 5 GOOG
AAA 6 GOOG
BBB 1 AMZN
BBB 2 AMZN
BBB 3 AMZN
CCC 1 AAPL
CCC 2 AAPL
CCC 3 AAPL
DDD 1 TSLA
DDD 2 TSLA