Question about selecting data from a column with dictionary

Community Support
1

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

2

Try 
select from tradeDict where ($dictcode["codename2"])=$“codevalue2”

3

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

4

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 }[“codename3”] each dictcode]

5

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)

6

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

http://code.kx.com/wiki/JB:QforMortals/lists#Elided_Indices

7

Yes that is the document.

thanks Rory