Only show dimensions linked to main data set

[#1]

Hi,

I have what would seem to be a very basic question, but I cannot figure it out after hours of trying and searching on these forums.

I am pulling data from Access via ODBC file. In the access database, there is a primary table and secondary tables.

The primary is like this (core data):

ID     Title            Description               Requestor     Grantor   

4      My Title         My description            1             2

5      My other title   My other description      1             3

The secondary table example is like this (user data):

ID     First Name     Last Name     User Name

1      Paul           Smith         Paul

2      Gene           Taylor        Gene

3      Joe            Haskell       Joe

4      Mary           Barnes        Mary

5      Sandy          Klein         Sandy

Since I want to bring in the data clean, I use a field alias when I load the user data table so that "User Data.ID" has the same field name in QV as "Core Data.Requestor" (and the same for "Core Data.Grantor"). From this, I can pull the field User Name which shows me the text name instead of a numeric key value.

The problem is, I have a List Box where I link to the field for User Name, and it shows me every User Name. I would prefer that the List Box only show the names from the Core Data. Example below:

What I want:

ListBox1

Paul

Gene

Joe

What I get:

ListBox1

Paul

Gene

Joe
Mary

Sandy

(this is just an example, so extrapolate it by an entire organization and you have a few thousand names in the list box that are not even relevant to the data)

There must be some way for QV to only show the names applicable to the core set, or to load in the data from the User Data table such that it only brings in the relevant names.

Message was edited by: dinkydonuts Re-worded my problem as I was not clear enough before.

[MayilVahanan]

Hi,

     Can you say,

ID     Title            Description               Requestor     Grantor   

4      My Title         My description            1             2

5      My other title   My other description      1             3

ID     First Name     Last Name     User Name

4      Mary           Barnes        Mary

5      Sandy          Klein         Sandy

Only in this example?

if so, use like this

Test1:
Load * inline
[
ID,Title,Description,Requestor,Grantor
4, My Title,My description,1,2
5, My other,title,My other description,1,3
];

inner join(Test1)
Load * inline
[
ID,First Name,Last Name,User Name
1,Paul,Smith,Paul
2,Gene,Taylor,Gene
3,Joe.Haskell,Joe
4,Mary,Barnes,Mary
5,Sandy,Klein,Sandy
];

Hope it helps

[#3]

Hi Mayil,

Sorry I did not clarify before. The ID field on both tables is not a key in the traditional sense.

As you see in the example, in the Core Data, ID #4 is linked to Requestor #1, which is user Paul.

I believe this is part of the reason why QV is not working 100% as I want. Unfortunately, I cannot modify the data model since this is coming from a MS Sharepoint list.

[cesaraccardi · Accepted solution]

Hi,

You can clean the non-related field values without changing the data model by using the function EXISTS() , that means you must first load the main table and for each following dimension you filter in the where clause.

Main:

ID     Title            Description               Requestor     Grantor  

4      My Title         My description            1             2

5      My other title   My other description      1             3

Secondary:

Load * inline

[

ID,First Name,Last Name,User Name

1,Paul,Smith,Paul

2,Gene,Taylor,Gene

3,Joe.Haskell,Joe

4,Mary,Barnes,Mary

5,Sandy,Klein,Sandy

] WHERE EXISTS(Requestor, "User Name") or EXISTS(Grantor,"User Name");

The first parameter is the existing field you are looking and the second is the field you are currently loading.

Regards,

Cesar

[#5]

Cesar,

Spot on! Thank you very much! That did exactly what I wanted it to.