join based on concat of two fields

[Anonymous]

I have a data set where I want to join to another data set by the concat of two fields.

example

MATERIAL	SAP REV ROW	concat
123456	A	123456A

so the concat field is what I would join the other table with.  I was thinking that I do this in the script upon the load...rather than load it twice

[avinashelite · Accepted solution]

Hi

Try like this

Load

MATERIAL&SAP REV ROW as field_name

from tablename

[simenkg]

You create a composite key in your script:

Load *, MATERIAL & [SAP REV ROW] as %ConcatKey Inline [

MATERIAL, SAP REV ROW

123456,A

];

[puttemans]

Hi there,

The script should look like this:

Table 1:

load *,

     MATERIAL&SAP REV ROW as linkname

FROM....

noconcatenate

Table 2  :

Load *,

    MATERIAL&SAP REV ROW as linkname

FROM....

(right/left/...)Join

Load *

Resident Table1;