Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
petersussinger
New Contributor

join two tables by substring

Hello,

I am trying to join two tables by a substring like is shown with a picture below, but I didn't have any luck with it.

Can you please help me.

substringjoin.JPG

As you can see in the picture, I want to join Table2 to Table1, where Field C is primary key and a substring to Field A from Table1.

I would appreciate some help and thank you on advance

1 Solution

Accepted Solutions
MVP
MVP

Re: join two tables by substring

I have worked out something. , yeah, it's a bit tricky.

Table1:

Load * Inline [

FieldA, FieldB

abcde, 123

fghik, 456

lmnop, 789

qrstu, 111

];

Table2:

Load * Inline [

FieldC, FieldD

rst, 222

mno, 333

ghi, 444

bcd, 555

];

Map:

Mapping Load

  FieldC,

  '_'&FieldC&'_'&FieldD as New

Resident Table2;

Final:

Load

  SubField(MapSubString('Map', FieldA), '_',2) as FieldC,

  FieldA,

  FieldB

Resident Table1;

Join

Load

  FieldC,

  FieldD

Resident Table2;

Drop table Table1, Table2;


Note: Under-score ('_') as been taken to separate out the sub strings assuming there would not be any '_' in your key fields.

4 Replies
Employee
Employee

Re: join two tables by substring

If it always is character 2,3, and 4 from FieldA that should be used, then you can create a new field

  Mid(FieldA, 2, 3) as Key

and make your join on this key.

But I suspect you want a more general solution, and if so, it could be a challenge, I would explore the option of creating a mapping table from table 2 and use mapsubstring() instead of a join.

HIC

nagaiank
Valued Contributor III

Re: join two tables by substring

You may use the following script and the table "Final" has the desired result.

Table1:

Load * Inline [

FieldA, FieldB

abcde,123

fghjk,456

lmnop,789

qrstu,111

];

Table2:

LOAD * Inline [

FieldC, FieldD

rst,222

mno,333

ghj,444

bcd,555

];

Outer Join (Table1) LOAD * Resident Table2;

Drop Table Table2;

Final:

NoConcatenate

LOAD FieldA, FieldB, FieldC, FieldD Resident Table1

where Index(FieldA, FieldC) > 0;

Drop Table Table1;

MVP
MVP

Re: join two tables by substring

I have worked out something. , yeah, it's a bit tricky.

Table1:

Load * Inline [

FieldA, FieldB

abcde, 123

fghik, 456

lmnop, 789

qrstu, 111

];

Table2:

Load * Inline [

FieldC, FieldD

rst, 222

mno, 333

ghi, 444

bcd, 555

];

Map:

Mapping Load

  FieldC,

  '_'&FieldC&'_'&FieldD as New

Resident Table2;

Final:

Load

  SubField(MapSubString('Map', FieldA), '_',2) as FieldC,

  FieldA,

  FieldB

Resident Table1;

Join

Load

  FieldC,

  FieldD

Resident Table2;

Drop table Table1, Table2;


Note: Under-score ('_') as been taken to separate out the sub strings assuming there would not be any '_' in your key fields.

petersussinger
New Contributor

Re: join two tables by substring

Thank you all very much for your answers and effort. You helped me a lot

Community Browser