cancel
Showing results for 
Search instead for 
Did you mean: 

Question about selecting data from a column with dictionary

carfield1
New Contributor
Hi all

I've a table with trade records there, every trade record have difference condition code relate with it. Say for trade1, it can have

codename1=codevalue1
codename2=codevalue2
codename3=codevalue3

Currently the trade table model as

date,time,price,size,codenames,codevalues

Where in the above example, codenames will be "codename1;codename2;codename3" and codevalues will be "codevalue1;codevalue2;codevalue3", both in string.

Now I need to filter the record only by codevalue2, so first I covert codenames and codevalues  to a new dictionary column:

tradeDict:update dictcode:{ vs[";";x[0]]!vs[";";x[1]]} each flip(codenames; codevalues) from trade

It look like working, if I run

exec last dictcode from tradeDict

I can see the dictionary of code name and value at last row in a dictionary form

However, if I run query

select from tradeDict where `$dictcode["codename2"]=`$"codevalue2"

I get the type error, just wonder, how can I query a dictionary column in a table?

Thanks
6 REPLIES 6

Eric_Suen
New Contributor
Try 
select from tradeDict where (`$dictcode["codename2"])=`$"codevalue2"

Thanks, but it is the same, still get type error

This should work:

tradeDict:update dictcode:{ (vs[";";x[0]])!vs[";";x[1]]} each flip(codenames; codevalues) from ([] codenames: ( "codename1;codename2;codename3";"codename4;codename5;codename6"); codevalues: ("codevalue1;codevalue2;codevalue3";"codevalue4;codevalue5;codevalue6"))
select  from tradeDict where like/:["codevalue3";{ y[x] }["codename3"] each dictcode]

Hi Carfield
You need to elide into the dictionary.
select from tradeDict where (`$dictcode[;"codename2"])=`$"codevalue2"
Thanks Rory

q)trade
time                          price    size codenames codevalues
----------------------------------------------------------------
2017.05.01D00:45:11.893963000 2.329662 73   "c3;d4"   "11;33"
2017.05.01D00:45:11.893963000 2.50046  0    "b2;c3"   "11;33"
2017.05.01D00:45:11.893963000 0.737272 134  "c3;d4"   "33;44"
2017.05.01D00:45:11.893963000 3.186642 166  "a1;b2"   "33;44"
2017.05.01D00:45:11.893963000 1.872634 141  "a1;c3"   "22;33"
2017.05.01D00:45:11.893963000 8.416288 192  "b2;c3"   "11;33"
2017.05.01D00:45:11.893963000 7.250709 137  "a1;b2"   "11;33"
2017.05.01D00:45:11.893963000 4.81804  165  "b2;c3"   "33;44"
2017.05.01D00:45:11.893963000 9.351307 191  "a1;c3"   "33;44"
2017.05.01D00:45:11.893963000 7.093398 30   "b2;c3"   "33;44"
q)tradeDict
time                          price    size codenames codevalues dictcode

--------------------------------------------------------------------------------
--------
2017.05.01D00:43:44.800254000 5.422726 56   "c3;d4"   "11;33"    ("c3";"d4")!("1
1";"33")
2017.05.01D00:43:44.800254000 6.116582 100  "b2;c3"   "11;33"    ("b2";"c3")!("1
1";"33")
2017.05.01D00:43:44.800254000 3.414991 63   "c3;d4"   "33;44"    ("c3";"d4")!("3
3";"44")
2017.05.01D00:43:44.800254000 9.516746 28   "a1;b2"   "33;44"    ("a1";"b2")!("3
3";"44")
2017.05.01D00:43:44.800254000 1.169475 51   "a1;c3"   "22;33"    ("a1";"c3")!("2
2";"33")
2017.05.01D00:43:44.800254000 8.158957 64   "b2;c3"   "11;33"    ("b2";"c3")!("1
1";"33")
2017.05.01D00:43:44.800254000 6.091539 196  "a1;b2"   "11;33"    ("a1";"b2")!("1
1";"33")
2017.05.01D00:43:44.800254000 9.830794 43   "b2;c3"   "33;44"    ("b2";"c3")!("3
3";"44")
2017.05.01D00:43:44.800254000 7.543122 189  "a1;c3"   "33;44"    ("a1";"c3")!("3
3";"44")
2017.05.01D00:43:44.800254000 3.813679 11   "b2;c3"   "33;44"    ("b2";"c3")!("3
3";"44")
q)select from tradeDict where (`$dictcode[;"b2"])=`$"11"
time                          price    size codenames codevalues dictcode

--------------------------------------------------------------------------------
--------
2017.05.01D00:43:44.800254000 6.116582 100  "b2;c3"   "11;33"    ("b2";"c3")!("1
1";"33")
2017.05.01D00:43:44.800254000 8.158957 64   "b2;c3"   "11;33"    ("b2";"c3")!("1
1";"33")
q)

Great, thanks a lot Rory, it working, btw, it is the corresponding document, right?

Yes that is the document.
thanks Rory