ssr on column of double quotes

Community Support
1

Hello all.  

I’ve got a data source with a column that I’m struggling to clean up in kdb as it is a symbol of double quotes (“”)  I was hoping to simply make these null then fill the nulls with the previous value.

For refence, I’ve looked at this helpful blog page but still haven’t been able to wrangle this annoying column column of data.  https://kx.com/blog/escape-double-up-quotes/

Below is an example of the data I’ve created to inspect and to see if could get it to use ssr. I would like the column symStream to just simply repeat the prev ticker for each id and seg and I thought a simple ssr would do it however its been a pain to sort this due to these pesky double quotes or even as the string of the double quotes (“""”)

Many thanks in advance!

q)t: update KEYWORD: `$ssr[;“”“”;“”] each string KEYWORD from t;

'length

  [0]  t: update KEYWORD: `$ssr[;“”“”;“”] each string KEYWORD from t;

Many thanks in advance!

id           seg symStream strStream  ---->> 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”                  TSLA

DDD         2   “”             “""”                     TSLA

2

Hi,

I think the problem is that you need to escape the double quotes within the ssr function, so doing 

q)t: update KEYWORD: fills `$ssr[;“""”;“”] each string KEYWORD from t;  

should work. 

Notice the fills keyword used also to fill forward with the last non-null value.

Hope this helps!

3

Perfect, thank you - that did the trick!

4

I don’t think it’s a good idea to represent null entries as “” casted as a symbol, you should represent them as null symbols (ex. `). Casting empty quotes to symbol will convert the value to null symbol:

q)`$(“GOOG”;“”;“”;“”;“”;“AMZN”)

`GOOG`````AMZN

Working with null symbols will be a lot easier when it comes to filling data in and other operations, and will be a lot faster than string logic , see below example for implementation

q)show data:(“SJS”;1#“,”) 0: (“id,seg,symStream”;“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,”)

id  seg symStream

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 symStream by id from data

id  seg symStream

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

5

Thank you Alvi, its worth noting the speed.  I had been originally omitted the data upon load from the source but we run into this a number of times and this character has significance in the logic so its good to have a variety of options to sort it out.

<o:p> </o:p>

Thanks!

<o:p> </o:p>

From: Alvi Kabir
Sent: Monday, October 12, 2020 4:04 AM
To: Kdb+ Personal Developers
Subject: [personal kdb+] Re: ssr on column of double quotes

<o:p> </o:p>

I don’t think it’s a good idea to represent null entries as “” casted as a symbol, you should represent them as null symbols (ex. `). Casting empty quotes to symbol will convert the value to null symbol:

<o:p> </o:p>

q)`$(“GOOG”;“”;“”;“”;“”;“AMZN”)

`GOOG`````AMZN

<o:p> </o:p>

Working with null symbols will be a lot easier when it comes to filling data in and other operations, and will be a lot faster than string logic , see below example for implementation

<o:p> </o:p>

q)show data:(“SJS”;1#“,”) 0: (“id,seg,symStream”;“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,”)

id  seg symStream

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

<o:p> </o:p>

q)update fills symStream by id from data

id  seg symStream

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

<o:p> </o:p>

On Monday, October 5, 2020 at 12:18:24 PM UTC-4 cjlew...@gmail.com wrote:

Perfect, thank you - that did the trick!

<o:p> </o:p>

On Monday, October 5, 2020 at 10:39:40 AM UTC-5 ddonnelly wrote:

Hi,

<o:p> </o:p>

I think the problem is that you need to escape the double quotes within the ssr function, so doing 

q)t: update KEYWORD: fills `$ssr[;“""”;“”] each string KEYWORD from t;  

<o:p> </o:p>

should work. 

Notice the fills keyword used also to fill forward with the last non-null value.

<o:p> </o:p>

Hope this helps!

On Monday, October 5, 2020 at 3:30:51 PM UTC+1 cjlew...@gmail.com wrote:

Hello all.  

I’ve got a data source with a column that I’m struggling to clean up in kdb as it is a symbol of double quotes (“”)  I was hoping to simply make these null then fill the nulls with the previous value.

<o:p> </o:p>

For refence, I’ve looked at this helpful blog page but still haven’t been able to wrangle this annoying column column of data.  https://kx.com/blog/escape-double-up-quotes/

<o:p> </o:p>

Below is an example of the data I’ve created to inspect and to see if could get it to use ssr. I would like the column symStream to just simply repeat the prev ticker for each id and seg and I thought a simple ssr would do it however its been a pain to sort this due to these pesky double quotes or even as the string of the double quotes (“""”)

<o:p> </o:p>

Many thanks in advance!

<o:p> </o:p>

q)t: update KEYWORD: `$ssr[;“”“”;“”] each string KEYWORD from t;

'length

  [0]  t: update KEYWORD: `$ssr[;“”“”;“”] each string KEYWORD from t;

<o:p> </o:p>

Many thanks in advance!

<o:p> </o:p>

<o:p> </o:p>

id           seg symStream strStream  ---->> 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”                  TSLA

DDD         2   “”             “""”                     TSLA

–
You received this message because you are subscribed to a topic in the Google Groups “Kdb+ Personal Developers” group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/personal-kdbplus/bTyj2CrsGM8/unsubscribe.
To unsubscribe from this group and all its topics, send an email to personal-kdbplus+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/personal-kdbplus/cd05fe74-c03d-4bc8-b4b4-0f5f04a6a23cn%40googlegroups.com.

<o:p> </o:p>