Re: Transform Specific Column Values

[rocuinneagain]

Without knowing the full rules and logic here are some thoughts.

//Table taken from https://www.nasdaqtrader.com/trader.aspx?id=CQSsymbolconvention
q)symbology:`NASDAQIntegratedPlatformSuffix xkey .Q.id ("****";enlist ",")0:`:symbology.csv
q)5 sublist symbology
NASDAQIntegratedPlatformSuffix| Security CQSSuffix CMSS..
------------------------------| ---------------------------------------------..
,"-"                          | "Preferred" ,"p" "PR"..
"-A"                          | "Preferred Class "A"*" "pA" "PRA..
"-B"                          | "Preferred Class "B"*" "pB" "PRB..
".A"                          | "Class "A"*" ".A" ,"A"..
".B"                          | "Class "B"*" ".B" ,"B"..

 

//Assuming 4 character root
q){s:string x;`$(4#s),symbology[4 _ s]`CQSSuffix} each (`$"AAPL.B*";`$"AAPL^#")
`AAPL`AAPLrw

 

//Assuming there is always a suffix and first non capital letter is beginning of suffix
{s:string x;r:first where not s in .Q.A;`$(r#s),symbology[r _ s]`CQSSuffix} each (`$"AAPL.B*";`$"AAPL^#")
`AAPL`AAPLrw

Note: ‘.B*’ does not appear in that mapping table so ‘AAPL.B*’ does not receive a new CQS Suffix.