Doing lookup in web intelligence

[former_member200808]

Dear experts,

I have two queries in my report, based on two separate data sources. Query A returns 100 usernames (object name 'usnm'), and contains only that single object; Query B returns 18,000 usernames (object name 'username'), along with other objects I need such as user location. What I want is a list of user locations (held in Query B) for the 100 users returned by Query A. Can you advise?

Here is what I have tried so far:

1. Query level - In query B, add username object as a filter and select 'result from object in another query' = 'usnm'. This did not work - database error, 'more than 1000 expressions in list'.

2. report level - merge 'usnm' and 'username' objects, and then make 'user location' a detail of that merged dimension. This did not work. I thought it would restrict the list to 100, but instead the merged dimension returns the 18,000 usernames!

3. report level - create a variable with an IF formula:

=If([usnm]=[User Name];[usnm];0)

This did not work. Neither did using Inlist, as this requires a literal list of values to be specified.

Thanks in advance for your help! 🙂

[former_member198519]

Check this as well in addition to what Amit suggested:

http://www.gulland.com/wp/?p=386

[amitrathi239]

check attached link.

https://blogs.sap.com/2014/12/08/using-merged-dimensions-to-perform-set-operations-union-intersectio...