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

1 Solution

Accepted Solutions
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

View solution in original post

12 Replies
chiru_thota
Specialist
Specialist

Hi,

Have you tried applymap() function ?

Thanks

chiru.

Not applicable

Hi, i think you are looking for function called applymap().

The problematic Key1 field could be clensed by KeepChar() if the problem is only with numbers, that depends on what values could be found on Key1 in fact.

The mapping requires that the first table would be loaded as a mapping load, it will not be displayed later on in the application.

Hope this helps with your problem, if it is in fact more complicated maybe some example of the data that you can face in the Key1 could help me understand what are you dealing with in fact.

Kind regards,

Lukasz

Not applicable

Hey Sergejs,

You can use Purgechar and then just join them. Please check out the attached file.

Thanks

AJ

zekazak
Creator
Creator
Author

Hi,

Thank you for the quick responses.

If I use applymap() it will find just one record which is Key1=Key=edf, but I'm trying to get all the records where Key = Key1 or Key = part of Key1.

KeepChar() also will not help as the part of the string can contain numbers and letters. So like this:

Key1:

abc123

abc 123

abcfff

Best Regards,

~Sergejs

zekazak
Creator
Creator
Author

Hi Ajy,

Yep. I've been offered to use KeepChar(), but it will not work for me as the part I want to get rid of from Key1 can contain the letters as well.

Best Regards,

~Sergejs

Not applicable

And there is no general rule for this Key1 field that we could use to clean it up ?

I would rather go maybe to the source of the data to clean it up a bit.

As only idea (rather bad one ) i have is an inner join which would match all values with all other values, then when you have it in one table you could use the wildmatch fucntion  in where clause for Key and Key1 fields, so in the table you would have only matching lines of data, and this table could be then used for applymap().

Kind regards,

Lukasz

zekazak
Creator
Creator
Author

That's the problem that there is no general rule to extract the Key from Key1. Maybe it's possible to use wildmatch somehow? or something like this?

~Sergejs

Not applicable

I would give a try to inner join table 1 and table 2, then i woudl load that table residently with a clasue: WHERE wildmatch(Key1,'*'&Key&'*'); and this should return hopefully matched values, i do not know if you can have some multiplication in this, depends on the values in Key1 and Key fields.

Then you could use this table for mapping load.

It is possible that the strings could repeat itself i do not know if this would be "bulletproof".

But maybe it is worth a try if the tables are not to big.

Kind regards,

Lukasz

whiteline
Master II
Master II

Hi.

I guess that keys are not supposed to have three symbols

So what you want to do if there is some ambiguity ?