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
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
That approach worked for me, thank you!
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