Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
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
Highlighted
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.

View solution in original post

4 Replies
Highlighted

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

Highlighted
Specialist III
Specialist 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;

Highlighted
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.

View solution in original post

Highlighted
Contributor
Contributor

Re: join two tables by substring

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