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.
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.
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 ?
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.
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?