Hello,
I trying to do a left outer join between two data providers.
I have a number field in a table and a set if numbers in an Excel file.
I’m trying to get all the numbers in the table that are not in the Excel file.
I tried to use a data provider for the table and one for the Excel file and linked the two number fields, but it doesnt work.
Appreciate your time.
queryanalyzer (BOB member since 2005-08-28)
The common dimension if of Char type.
When i display both the dimensions in a table they bothe show the exact same value, though the Excel file does not have as many rows as the table does.
Shouldnt this be a full outer join and show ‘NULL’ if the corresponding value doent exist in the other data provider.
I looked at this…
Thanks.
queryanalyzer (BOB member since 2005-08-28)
It was my fault.
I was retrieving only the 2 common dimensions.
Looks like i have to get a measure, into the report, and apply a filter on the common dimension based on this measure, check if it is null/ not null.
One think i still dont understand is this,
Ex:
Table 1 (Column 1)
1
2
3
5
6
Excel file values (Column 2)
1
2
3
4
5
6
7
When i create 2 data provides and link them based on the common dimension, this is what i get,
Column 1 | Column 2
1 1
2 2
3 3
4 4
5 5
6 6
7 7
It is showing values in Column 1 that doesnt exist in the table. Is this because of the linking ?
Thanks.
queryanalyzer (BOB member since 2005-08-28)
Yes
jac 
(BOB member since 2005-05-10)
When you link to items, the resulting values are “unioned” together. So if DP1 has values A, B, and C, and DP2 has X, Y, and Z, then the link (union) will have A, B, C, X, Y, Z. Note that there were zero items in common prior to the link. 
Dave Rathbun 
(BOB member since 2002-06-06)
Doesn’t this negate the old calendar work around where you could have a full list of months for this year?
For example current figures run 200601 through to 200606.
I want to display up to 200612 on a report so that the graph scale and the table remain constant throught the year.
In 5.1.6, say, I’d create an Excel data provider with one column, YearMonth and 12 rows of 200601 - 200612.
Change the definition from measure to dimension and link the two and you used to get all 12 months listed with only the first 6 populated.
No, it’s what makes it possible. Either that, or I am misunderstanding your comment.
Suppose you have monthly data for Jan and March only. In order to get Feb to show up, you include spreadsheet data. The “union” process fills in Feb from the XLS even though it’s not there in the data. Correct?
Dave Rathbun (BOB member since 2002-06-06)
It works if you just use the month.
As soon as I put location in, it’s thrown and goes back to inner join mode.
I don’t remember it working like that in v5