CODEWITHSUNDEEP
kdb+
Terry
Terry

Reputation: 519

How to pass dictionary into query constraint?

If this is the dictionary of constraint:

dictName:`region`Code;
dictValue:(`NJ`NY;`EEE213);
dict:dictName!dictValue;

I would like to pass the dict to a function and depending on how many keys there are and let the query react accordingly. If there is one key region, then I would like to put it as

select from table where region in dict`region;

The same thing is for code. But if I pass two keys, I would like the query knows and pass it as:

select form table where region in dict`region,Code in dict`code;

Is there any way to do this?

I came up this code:

 funcForOne:{[constraint]?[`bce;enlist(in;constraint;(`dict;enlist constraint));0b;()]};

funcForAll[]
{[dict]$[(null dict)~1;select from bce;($[(count key dict)=1;($[`region in (key dict);funcForOne[`region];funcForOne[`Code]]);select from bce where region  in dict`region,rxmCode in dict`Code])]};

It works for one and two constraint. but when I called funcForAll[] it gives type error. How should I change it? i think it is from null dict~1 I tried count too. but doesn't work too well.

Update

So I did this but I have some error 

    tab:([]code:`B90056`B90057`B90058`B90059;region:`CA`NY`NJ`CA);                                                                                             
dictKey:`region`Code;dictValue:(`NJ`NY;`B90057);
dict:dictKey!dictValue;
?[tab;f dict;0b;()];

and I got 'NY error. Do you know why? Also,if I pass a null dictionary it doesn't seem working.

Upvotes: 1

Views: 1461

Answers (2)

Manish Patel
Manish Patel

Reputation: 4491

If you're just using in, you can do something like:

f:{{[x;y](in),'key[y],'(),x}[;x]enlist each value[x]}

So that:

q)d
a| 10 1
b| ,`a
q)f d
in `a 10 1
in `b ,`a

q)t
a b c
------
1 a 10
2 b 20
3 c 30
q)?[t;f d;0b;()]
a b c
------
1 a 10

Note that because of the enlist each the resulting list is enlisted so that singletons work too:

q)d:enlist[`a]!enlist 1
q)d
a| 1
q)?[t;f d;0b;()]
a b c
------
1 a 10

Update to secondary question

This still works with empty dict, i.e. ()!(). I'm passing in the dictionary variable.

In your 2nd question your dictionary is not constructed correctly (also remember q is case sensitive). Also your values need to be enlisted. Look up functional select in the reference pages on the kx site, you'll see that you need to enlist the symbol lists to differentiate them from column name declarations

`region`code!(enlist `NY`NJ;enlist `B90057)

Upvotes: 0

Rahul
Rahul

Reputation: 3969

As I said funtional form would be the better approach but if your requirement is very limited as you said then you can consider other solution as below:

Note: Assuming all dictionary keys will be in table columns list.

q) f:{[dict] if[0=count dict;:select from t]; 
   select from t where (#[key dict;t]) in {$[any 0<=type each value x;flip ;enlist ]x}[dict] }

Explanation: 1. convert dict to table depending on the values type. Flip if any value is a general list else enlist.

            $[any 0<=type each value dict;flip ;enlist ]dict

  1. Get subset of table t which consists only of dictionary keys as columns.

           #[key dict;t]

  2. get rows where (2) in (1)

Basically we are using below form of querying and matching:

   q)t1:([]id:1 2;s:`a`b);
   q)t2:([]id:1 3 ;s:`a`b);
   q)select from t1 where ([]id;s) in t2

Upvotes: 0

Related Questions