Concatenation / Null Values where a key exists

[#1]

Hello,

I am trying to concatenate a table into my master table where there are several key fields that are in common in both tables.

When I concatenate them, there appears to be no link in those fields ie:

Master:

Table:    ** The table I want to be concatenated in

Master: ** this is the result after the concatenation:

What I want to happen is:

I have tried other joins of the table ie. outer join / join with no success as well as trim around the Code which is one of the links.

Any help is much appreciated - can't quite work out why this is not working as I would imagine it to...

Thanks

[Kushal_Chawda]

Instead of Concatenate  do left Join with master table. for eg,

Master:

LOAD * INLINE [

    Fruit, Code

    Apple,1111111

    Mago, 2222222

    Banana,333333

    Peach,444444

    Strawberry,55555

];

Left Join

LOAD * INLINE [

    OtherData, Code

    Spain,1111111

    France, 2222222

    Belgium,333333

    USA,444444

    Brazil,55555

];

[sasiparupudi1]

May be you have synthetic keys in the table..

[#4]

Correct, I do.... is this an issue?

[jonathandienst]

Kush is correct - you need a join.

Simply put:

Concatenate generally adds extra rows

Join is generally adds extra columns to the existing rows

[jagan]

Hi,

Instead of Concatenation you need to Join both the tables in this scenario, so that you will get the required ouput.

Regards,

Jagan.

[sasiparupudi1]

Your join should work fine if you resolve synthetic keys in your data model

[#8]

On a separate note to the above example but still related to concatenating and joining:

When I use a join I lose the data

When I concatenate I get Null values

and when I have a synthetic key I am able to calculate correctly...

[#9]

How is best to resolve a synthetic key when there are 3 key fields that are in common in both tables.

When I join, the data is not correct

When I concatenate I get null values

[sasiparupudi1]

You could  may be use a concatenated key between the tables

key1 &'||'& key2 &'||'&key3 as NewKey