Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
zekazak
Creator
Creator

Mapping question. Linking two tables

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

KeyValue
abcdisplayname1
edfdisplayname2

xyz

displayname3

2. table

Key1
abc123
edf
789xyz

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

KeyKey1Value
abcabc123displayname1
edfedfdisplayname2
xyz789xyzdisplayname3

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

12 Replies
maxgro
MVP
MVP


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

KeyKey1Value
abcabc123displayname1
edfedfdisplayname2
xyz789xyzdisplayname3
zekazak
Creator
Creator
Author

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
MVP
MVP

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