Joining Columns that Aren't Exactly the Same

[artempotsuray]

Hello, I have two data sources that I want to join. One of the sources comes from a oracle database and has a column named GAGE which contains Gage numbers that are of various lengths. The second data source is an excel file that has a column named GAUGE PREFIX which contains the prefix (first 4-5 letters of a Gage number).

For example from data source 1 I have:

GAGE  

CS123-100

CS123-200

CS124-100

CS124-200

...

And from data source 2 I have:

GAUGE PREFIX          TYPE

CS123                          Electrical

CS124                          Pressure

...

I want to combine the sources to get a result like:

GAGE                 TYPE

CS123-100         Electrical

CS123-200         Electrical

CS124-100         Pressure

CS124-200         Pressure

...

Anything will help. Thank You

[sunny_talwar · Accepted solution]

Why don't you create a new column within Oracle data source load...

LOAD GAGE,

      SubField(GAGE, '-', 1) as [GAGE PREFIX]

FROM ...;

Now join them on GAGE PREFIX

[artempotsuray]

That helped a lot but some of the gauges don't have a "-" as a delimiter but a '/' or another character. Could I write the code with multiple delimiters or would I have to write another SubField() statement. If so could I call it the same name?

[sunny_talwar]

May be like this

LOAD GAGE,

      SubField(SubField(GAGE, '-', 1), '/', 1) as [GAGE PREFIX]

FROM ...;