Cross filter two lookups where field contains other field

[RobHoz]

Hello, 
I'm trying to filter one lookup with the values of an other lookup.

This is the situation:

Lookup roles.csv contains the field with the security roles I would like to check for:

Role

role1

role2

role3

role6

Lookup AssignedRoles.csv contains a field with all the assigned roles 

User	Role
User1	role2 role5
User2	role6
User3	role9 role8
User4	role7 role4
User5	role1 role2

Now I want to return a table with all the users in AssignedRoles.csv that have an assigned Role from Roles.csv

Can anybody help me with an example query, if it is at all possible?

Thanks,
Robin

[RobHoz]

That approach worked for me, thank you!

[ITWhisperer · Accepted solution]

You could use a couple of nested joins

| makeresults count=50
| streamstats count as row
| eval User="User".(row-1)%10
| eval value=random()%100
| table User value


| join User 

    [| makeresults
    | eval _raw="User	Role
User1	role2 role5
User2	role6
User3	role9 role8
User4	role7 role4
User5	role1 role2"
    | multikv forceheader=1 
    | table User Role

    | eval Role=split(Role," ")
    | mvexpand Role
    | join 

        [| makeresults
        | eval _raw="Role 
role1
role2
role3
role6"
        | multikv forceheader=1 
        | table Role]

    ]

The makeresults generate dummy data, and the represent the inputlookups for your csv stores. I have assumed that the role lists are space separated and split them accordingly