Skip to Content
Aug 14, 2017 at 04:28 PM

Doing lookup in web intelligence

131 Views Last edit Aug 14, 2017 at 04:29 PM 2 rev

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! :)