CODEWITHSUNDEEP
kdb+q
Invictus
Invictus

Reputation: 4338

Return value from list of list when criteria met in KDB

I have a list of list which has 4 columns. Now I want to iterate over each list in list of list to find the first list which has say value ABC for first column and value XYZ for 3rd col. If this criteria satisfy I want to get out of the loop and return what was in 4th col. E.g of how list would look like below.

`AAA 23 `BBB  value
`AAA 23 `CCC  value
`ABC 23 `XYZ  value
`BBB 23 `CCC  value
......
.....

I tried to do something like below, but this does not seem to work.

myfunc:{if [x[0] =`ABC and x[2]=`XYZ ; val:x[3] ; 'break ] } 

@[myfunc;x; if [x[0] =`ABC and x[2]=`XYZ; :x[3]]] each ListOfList

Note that i do not want to traverse whole of list of list as it has millions of list in it, and is interested in fetching value of first list where criteria is matched.

Upvotes: 0

Views: 103

Answers (4)

terrylynch
terrylynch

Reputation: 13657

If your concern is that the list is very big and you don't want to scan the entire list then this approach could suit (increment index by one so long as criteria not true)

q)l:((`AAA;23;`BBB;`value1);(`AAA;23;`CCC;`value2);(`ABC;23;`XYZ;`value3);(`BBB;23;`CCC;`value4))
q)l[{not`ABC`XYZ~l[x;0 2]}(1+)/0;3]
`value3

Upvotes: 2

Alexander Unterrainer-DefconQ
Alexander Unterrainer-DefconQ

Reputation: 713

You can use nested indexing to retrieve the first and third element of each nested list respectively and then find ? to find the first occurrence. Note, find actually does exactly that, it returns the first match. https://code.kx.com/q/ref/find/

q)l:((`AAA;23;`BBB;`value);(`AAA;23;`CCC;`value);(`ABC;23;`XYZ;`value);(`BBB;23;`CCC;`value))
q)l
`AAA 23 `BBB `value
`AAA 23 `CCC `value
`ABC 23 `XYZ `value
`BBB 23 `CCC `value
q)l[::;0 2]
AAA BBB
AAA CCC
ABC XYZ
BBB CCC
q)l[::;0 2]?(`ABC;`XYZ)
2
q)l l[::;0 2]?(`ABC;`XYZ)
`ABC
23
`XYZ
`value
q)l2:l,l
q)l2
`AAA 23 `BBB `value
`AAA 23 `CCC `value
`ABC 23 `XYZ `value
`BBB 23 `CCC `value
`AAA 23 `BBB `value
`AAA 23 `CCC `value
`ABC 23 `XYZ `value
`BBB 23 `CCC `value
q)l2 l2[::;0 2]?(`ABC;`XYZ)
`ABC
23
`XYZ
`value
q)\ts:1000 l2 l2[::;0 2]?(`ABC;`XYZ)
1 1696

Alternatively, if you're looking for all occurrences (easily adjustable to first) you can create a table and then use below syntax

q)select from (`a`b`c`d!/:l2) where ([] a;c) in ([] a:enlist `ABC;c:`XYZ)
a   b  c   d
----------------
ABC 23 XYZ value
ABC 23 XYZ value

just the first

q)first select from (`a`b`c`d!/:l2) where ([] a;c) in ([] a:enlist `ABC;c:`XYZ)
a| `ABC
b| 23
c| `XYZ
d| `value

Upvotes: 2

rianoc
rianoc

Reputation: 3786

Maurice answer suggesting using efficient vector comparisons makes sense.

As a general structure holding data in tables can lead to more efficient and readable queries compared to working on nested lists:

q)l:((`AAA;23;`BBB;`value1);(`AAA;23;`CCC;`value2);(`ABC;23;`XYZ;`value3);(`BBB;23;`CCC;`value4))
q)t
x   y  z   k
-----------------
AAA 23 BBB value1
AAA 23 CCC value2
ABC 23 XYZ value3
BBB 23 CCC value4
q)exec first k from t where x=`ABC,z=`XYZ
`value3

Upvotes: 1

Maurice Lim
Maurice Lim

Reputation: 883

If all the list within the list has at least 4 entries then it might be more efficient to perform a vector comparison rather than using an iterator.

See: https://code.kx.com/q4m3/3_Lists/#310-elided-indices

list[;3]where(list[;0]=`ABC)&list[;2]=`XYZ / to get all the matching entries
list[;3]first where(list[;0]=`ABC)&list[;2]=`XYZ / to get only the first match

If you want to use a while loop:

{while[not[`ABC`XYZ~x[0:0 2]]&count x;x:1_x];x[0;3]}list

In your solutions, there are a few syntax errors.

  • In myfunc, if statement is not explicitly returning anything.
  • Your checks are wrong. The condition on the left needs to be bracketed as KDB evaluates from right to left.
  • Your second line is not wrapped into a function hence you are getting an 'x error. Even so, the error trap wouldn’t really return a value.

Upvotes: 2

Related Questions