Function across tables to build a dictionary of lists

[JP]

Good evening,

Newbie needing enlightenment here… I’ll simplify the problem and take 2 similar tables (except an extra column in tab1) with different counts:

q)tab1:([] cola:100?10f; colb:100?20f; spec:100?1f)

q)tab2:([] colv:10?10f; colw:10?20f)

q)10#tab1

cola      colb     spec       

------------------------------

6.088242   2.040346  0.687822   

4.432953   7.651239  0.3899851  

0.7565383  13.26234  0.7113442  

0.6863489  19.36054  0.5350818  

3.742612   0.3180383 0.7721675  

7.727166   6.810817  0.1281917  

3.451527   1.543278  0.4579373  

8.629411   17.13591  0.342629   

8.648804   7.610709  0.6085203  

7.979349   1.26617   0.0238227  

0.8280825  2.209655  0.9067614    

…

q)tab2

colv      colw     

-------------------

3.852997  12.77758

3.925736  18.4223 

8.73952   19.04304

1.733995  9.903678

3.859981  15.6167 

6.263786  10.31435

6.119325  19.3141 

5.427825  3.784675

0.2358859 19.51623

8.440508  8.814018

I need to go through each row of tab2 and pick up both tab2.colv and tab2.colw values, pass those to a function that will compute concurrent conditions cond1 (on tab1.cola) and cond2 (on tab1.colb) across each row of the whole tab1, and return, for each value in tab2.colw, a list of tab1.spec values that met both cond1 and cond2.  The following basic function does the job for a single column passed to it: 

fct:{[x] 

   cond1:tab1.cola within (0.95*x, 1.05*x);

   tab1.spec where (cond1)

  }

tab2.colv!fct each tab2.colv

which outputs correctly:

3.852997 | 0.7721675 0.9492766 0.1401676 0.5609396

3.925736 | 0.7721675 0.8073386 0.5609396

8.73952  | 0.342629 0.6085203 0.6092087 0.9011315 0.05570828

1.733995 | 0.3880959 0.232788

3.859981 | 0.7721675 0.1401676 0.5609396

6.263786 | 0.687822 0.7893679 0.5680335

6.119325 | 0.687822 0.5680335

5.427825 | ,0.7381316

0.2358859| `float$()

8.440508 | 0.006855978 0.3297093 0.05570828 0.6066074 0.8906859

… but I can’t seem to make it work by passing more than a single column (allowing cond2, cond3...). Also pretty sure there is something much more flexible and elegant than the above. Any help would be most appreciated! 

JP

[TerryLynch]

A couple of things:

1. I don't think (0.95*x, 1.05*x) is correct, you most likely want (0.95*x; 1.05*x) which is quite different

q)(0.95*8.664098, 1.05*8.664098)

8.230893 8.642438

q)(0.95*8.664098; 1.05*8.664098)

8.230893 9.097303

2. You can use the other columns by iterating over each row making your "x" a dictionary

fct2:{[x] cond1:tab1.cola within (0.95*x`colv; 1.05*x`colv); cond2:tab1.colb within (0.95*x`colw; 1.05*x`colw); tab1.spec where (cond1&cond2)}

q)fct2 each tab2

`float$()

,0.5759051

`float$()

`float$()

0.5919004 0.2392341

`float$()

`float$()

`float$()

`float$()

0.391543 0.3867353

3. You might be able to avoid iterating through each row with something like

q)select from (tab2 cross tab1) where cola within .95 1.05*\:colv, colb within .95 1.05*\:colw

colv     colw     cola     colb     spec

---------------------------------------------

5.281112 9.63608  5.126243 9.717858 0.5759051

5.464707 18.9044  5.459723 19.59456 0.5919004

5.464707 18.9044  5.635053 18.81905 0.2392341

8.154024 8.602661 8.132478 8.489755 0.391543

8.154024 8.602661 8.158957 8.203828 0.3867353

[JP]

Thanks Terry,

I definitely feel ashamed, as before posting I did exactly what you suggested... except my basic error in the (0.95*x; 1.05*x) range definition with the join ',' instead of the separator ';' was generating a 'type' error when executing fct2 each tab2, and I had no idea what was going on.  Thanks for pointing this critical mistake; I've learned again.  

Best,

JP