Skip to Content
0

Doing lookup in web intelligence

Aug 14, 2017 at 04:28 PM

45

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

AMIT KUMAR
Aug 14, 2017 at 08:38 PM
0
Share
10 |10000 characters needed characters left characters exceeded
Kuldeep Ghosh Aug 16, 2017 at 11:39 AM
0

Check this as well in addition to what Amit suggested:

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

Share
10 |10000 characters needed characters left characters exceeded