Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
];
May be you have synthetic keys in the table..
Correct, I do.... is this an issue?
Kush is correct - you need a join.
Simply put:
Concatenate generally adds extra rows
Join is generally adds extra columns to the existing rows
Hi,
Instead of Concatenation you need to Join both the tables in this scenario, so that you will get the required ouput.
Regards,
Jagan.
Your join should work fine if you resolve synthetic keys in your data model
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...
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
You could may be use a concatenated key between the tables
key1 &'||'& key2 &'||'&key3 as NewKey