Concatenating tables - identical records not being grouped

[adey74]

Hi everyone,

I am concatenating two tables, with numerous fields. Several fields are common across both tables.
In particular, there is a dimension called 'CurrencyCode' that contains - you guessed it - a currency indicator, e.g. AUD, USD, GBP etc.

This dimension exists in both tables. What I need to do is group my selected measure by this dimension. This s/be straightforward, if the format of this field is the same in each table, i.e. text.

After concatenating the two tables, I now have duplicate currency codes. e.g. 2 AUD indicators, 2 USD indicators etc. 

Data from one table is displayed against one indicator, and data from the other table is displayed against the other one:

I am expecting to see 1 record, not 2

The field length in each table is 3, they are both formatted as text. 

So, I do not understand why they are not grouped together, and I do not understand why, in the table above, they are aligned differently.

Help please.  

[Or]

Most commonly this means one of two things:

1) Your text has spaces in it (You can test using len() or just trim() the field)

2) This is being read as a Dual field (using Dual() or otherwise) and, since dual types are recognized as numeric, it is getting aligned to the right as a number would. You can test this by wrapping the field in a num() function and seeing if it does indeed return a number.

[adey74]

Yes, I have checked this and as stated, the length of each field is 3, as expected, with no spaces.
I have also tested each field using IsNum and IsText and each field is a text field.

[Or]

Just to be on the safe side, I'd suggest using text(CurrencyCode) as CurrencyCode in the script on both of these fields to see if that fixes it. Additionally, check the schema to make sure there isn't some other table causing duplication (either through an incorrect relationship or if another table adds CurrencyCode values other than these two), but that still wouldn't explain the justification issue, unless that's adding a dual() type or something of that nature. 

[Edit]

Another way to test if the currency code is the problem might be to just load that field from each of the two tables, and see if they join or not:

Currencies:

Load distinct CurrencyCode From Table1;

INNER JOIN

Load distinct CurrencyCode From Table2;

Or just count(Distinct CurrencyCode) in a text/table object and see if you get the correct number of currencies or not.

[rwunderlich]

What is the source of each table?

-Rob

[adey74]

Both from .qvds