topic join two tables by substring in QlikView

join two tables by substring

Anonymous — Fri, 09 Oct 2015 08:04:29 GMT

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.

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

Re: join two tables by substring

hic — Fri, 09 Oct 2015 09:03:42 GMT

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

Re: join two tables by substring

nagaiank — Fri, 09 Oct 2015 09:27:08 GMT

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;

Re: join two tables by substring

tresB — Fri, 09 Oct 2015 09:34:23 GMT

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.

Re: join two tables by substring

Anonymous — Fri, 09 Oct 2015 09:59:28 GMT

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