Some time back, when I was still getting familiar with qsql, I went for an interview at an investment bank. I was interviewed by 4 different people for a total of 2 hours. 3 of the interviewers asked theoretical architecture related questions but the last one was all about writing code. The last interviewer gave me a piece of paper and asked me to write a bunch of qsql statements for different scenarios. One of the questions had to do with parsing two columns that contained first and last name and then joining them together. At that time, I didn’t know how to do that. I didn’t get the job but I realized the importance of qsql and started learning. Later, I would come to realize the importance of functional queries.
Anyways, the point of the story is that you will face many situations during the course of your career where you will have to dissect columns to get your desired output. Yesterday, I had a text file with some data that I needed to dissect and put into a table so I can properly analyze it. In this post, I will discuss how I did that.
The original data looked like this:
SECMASTER::TAQ2::2958158 SECMASTER::TAQ2::3073733 SECMASTER::TAQ2::115854 TAQ2::VLY WS TAQ2::VLYW W SECMASTER::TAQ2::1612216 SECMASTER::TAQ2::36680 SECMASTER::TAQ2::3206178
I wanted to only focus on the data without SECMASTER and make it look like this:
sym1 sym2 ---------- VLY WS VLYW W
First, I had to read the data from a text file and put it into a table:
t:([]sym:read0`:mapping.txt)
Then, I removed any row that had SECMASTER in it:
m:select from t where not sym like "SEC*"
Then, I removed the TAQ1:: part by selecting anything from index 6 and onward:
m:select {trim x[6+til count x]}each sym from m
cheapest price for viagra They would surely guide you with the best deals possible. Some of the weird causes can be cycling, smoking, alcohol, long siting, sleeping problems online cialis pills etc. can also be the foe for males’ sexual health. Does that mean you will have vardenafil canadian pharmacy to compromise on sleep with the excuse of social commitments or weekend lifestyle. A nail infection usually begins as a peripheral disorder in which pain originates within the muscle. free tadalafil
Finally, I selected part of the row and put it into sym1 and put the rest in sym2. I used x?" " to determine where the first part ended.
j:select sym1:{`$trim x[til x?" "]}each sym,sym2:{`$trim x[(x?" ")+til ((count x)-1)]}each sym from m
This will give you:
q)j sym1 sym2 ---------- AA PR AA PRB ABR PRA ABR PRB ABR PRC ACT PRA ADK PRA ADXS W AES PRC AF PRC AFSI PRA AFSI PRB AFSI PRC AFSI PRD AGII L
Once you have data in such a format, you can do your analysis.
I am sure there is a better way to do this. If you know of one, feel free to leave a comment!
Update:
Rolf’s version:
Version 1: exec`sym1`sym2!/:{`$(x 0;last x)}@'" "vs'6_'sym from t where sym like"TAQ*"
Version 2: exec flip`sym1`sym2!flip{`$(x 0;last x)}each" "vs'6_'sym from t where sym like"TAQ*"
David’s version:
flip `sym`exch!(" SS";5 6 3)0:d,'(14-count each d)#\:" "