Searching result using two inputlookup collections

[ssamant007]

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)

[ITWhisperer · Accepted solution]

Try something like this

| inputlookup collectionA | search [| inputlookup collectionB | fields fieldX | rename fieldX as field1 | format]

[ssamant007]

Thanks @ITWhisperer .

[ssamant007]

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.