Linking Fields

[#1]

Hello,

I have data from two tables with the same name, Program ID. However, the data in one table has the prefix. "PRG-" before the program number and the other filed only has the number.

To illustrate:

Table 1 -

Program ID:

1010102

1010103

1010104

Table 2 -

Program ID:

PRG-1010102

PRG-1010103

PRG-1010104

How can I make these two join in the load statement?

[sunny_talwar · Accepted solution]

May be like this:

LOAD [Program ID),

          ...

FROM Table1;

Join

LOAD [Program ID] as [Original Program ID],

          SubField([Program ID], '-', 2) as [Program ID]

          ....

FROM Table2;

[#3]

Sunny,

SubField([Program ID], '-',2)

Does this cut the "PRG-" from the fields to join at the numbers?

[sunny_talwar]

Yes, is it not working? May be you need to do this:

Num#(SubField([Program ID], '-',2))

or

SubField([Program ID], '-',2) * 1

[#5]

Here is my scrip, where do you add the join?

Directory;
LOAD [Program ID],
Address,
SKU
FROM
Book1.xlsx
(ooxml, embedded labels, table is Table2);

Directory;
LOAD [Program ID],
[Program Revision],
Name
FROM
Book1.xlsx
(ooxml, embedded labels, table is Table1);

[sunny_talwar]

May be like this:

Table:
LOAD Num#(SubField([Program ID], '-',2)) as [Program ID],
     Address,
     SKU
FROM
Book1.xlsx
(ooxml, embedded labels, table is Table2);

Join (Table)
LOAD [Program ID],
     [Program Revision],
     Name
FROM
Book1.xlsx
(ooxml, embedded labels, table is Table1);

[MarcoWedel]

maybe also possible:

LOAD Mid([Program ID],5) as [Program ID]

...

FROM ...

hope this helps

regards

Marco

[hemachandran]

hi,

Try this expression,

Table2:
LOAD Num#(SubField([Program ID]),'-',2)) as [Program ID]
     Address,
     SKU
FROM
Book1.xlsx
(ooxml, embedded labels, table is Table2);

Join
LOAD [Program ID],
     [Program Revision],
     Name
FROM
Book1.xlsx
(ooxml, embedded labels, table is Table1);

• Linking Fields

[#9]

Hi Sunny,

Your first suggestion was correct and I am marking it as such. However, the article The Secret Life of SubField was very helpful in knowing exactly how to setup my script. 

The Secret Life of SubField

I used this for my script:

LOAD Distinct
Subfield("Program_ID", '-') as [Program_ID],

I didn't need a join statement since the other table had the same Program ID after the '-'. My connection is as expected and returning the data properly.

Thanks!

[#10]

Sunny,

One clarification: Subfield([Program ID], '-',2)

What is the 2 doing? And is there a way to have the same work for data before the '-'?