Mapping question. Linking two tables

[zekazak]

Hello,

I've got to link two tables where the key field of the first table is the part of a key field of the second table.

1. table

Key	Value
abc	displayname1
edf	displayname2
xyz	displayname3

2. table

Key1
abc123
edf
789xyz

So I would like to link these two tables and get the result something like this:

Key	Key1	Value
abc	abc123	displayname1
edf	edf	displayname2
xyz	789xyz	displayname3

i found mapsubstring function but It seems will not work for this case. Have you got any advise about this?

Thank you for you help in any case.

~Sergejs

[maxgro]

join are equi join in qlikview so I think to compare fields of two different tables you have to do before a full outer join and after a compare of the 2 fields

SCRIPT

table1:

LOAD * INLINE [

Key, Value

abc, displayname1

edf, displayname2

xyz,displayname3

];

table2:

LOAD * INLINE [

Key1

abc123

edf

789xyz

];

tmp:

load Key Resident table1;

join (tmp) load Key1 Resident table2;

link:

load * where Flag = 1;

NoConcatenate load

Key,

Key1,

if(WildMatch(Key1, '*' & Key & '*'), 1, 0) as Flag

Resident tmp;

DROP Table tmp;

RESULT

Key	Key1	Value
abc	abc123	displayname1
edf	edf	displayname2
xyz	789xyz	displayname3

[zekazak · Accepted solution]

Hi,

Your code works but very slow. I guess it because of WildMatch. So finally I've ended up with the code like this which gives me the expected result and works quite fast:

table1:

Load *

INLINE [

Key, Value

abc, displayname1

edf, displayname2

xyz,displayname3

];

table2:

Load*

INLINE [

Key1

abc123

edf

789xyz

];

t1:

mapping LOAD Key,

  '<'&Key&'>'  as Key_Copy

resident table1; 

t2:

LOAD Key1, 

  textbetween(MapSubString('t1',Key1),'<','>') as Clean_Key1

resident table2;

left join (t2)

load Key as Clean_Key1, Value

resident table1;

drop table table1, table2;

~Sergejs

[maxgro]

Good solution Sergejs (marks your answer as correct, so other people can find it)

I think the reason mine is slower is I works on a cartesian of table1 (N record) and table2 (M record), so N * M record

You works on table2, M record.

Congratulations again for your textbetween(mapsubstring...........