Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Linking Fields

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?

1 Solution

Accepted Solutions
sunny_talwar

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;

View solution in original post

10 Replies
sunny_talwar

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;

Not applicable
Author

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

Not applicable
Author

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
Partner - Creator
Partner - Creator

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);

Not applicable
Author

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!

Not applicable
Author

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 '-'?