I need to find the rows from the first inputlookup collection that has matching field values from the second inputlookup collection.
For example:
collection A : field1, field2, field3
X 1 3
X 2 4
Y 4 1
Z 1 2
B 3 3
B 1 1
CollectionB: fieldX
X
Y
B
The expected result is: (exclude row containing 'Z' as it does not entry in collectionB)
field1, field2, field3
X 1 3
X 2 4
Y 4 1
B 1 1
the query like:
| inputlookup collectionA | search field1 IN ('X','Y','Z'....).
How can I set values 'X','Y','Z'.... to search for field1 from collectionB as this list can be of any length. I tried the following but didn't work:
| inputlookup collectionA | search field1 IN (| inputlookup collectionB |fields fieldX). (as in reality the collectionB can have more than one columns but I want to match values only with fieldX)
Try something like this
| inputlookup collectionA | search [| inputlookup collectionB | fields fieldX | rename fieldX as field1 | format]
Try something like this
| inputlookup collectionA | search [| inputlookup collectionB | fields fieldX | rename fieldX as field1 | format]
Thanks @ITWhisperer .
What if I need to match with only subset of fieldX values from collectionB into the field1 values from collectionA assuming the collectionB has other fields .
Like
collectionB:
fieldX, fieldY
Y ss
A zz
B yy
i.e. first I need to extract results from collectionB based on column 'fieldY' say fieldY='zz'
| inputlookup collectionA | search [| inputlookup collectionB | search fieldY ='zz' | fields fieldX | rename..... but it didnt serve my required results. It is returning all the rows from collection A.