topic Re: Mapping question. Linking two tables in QlikView

Mapping question. Linking two tables

zekazak — Wed, 19 Feb 2014 15:26:48 GMT

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

Re: Mapping question. Linking two tables

chiru_thota — Wed, 19 Feb 2014 15:36:28 GMT

Hi,

Have you tried applymap() function ?

Thanks

chiru.

Re: Mapping question. Linking two tables

Wed, 19 Feb 2014 15:41:31 GMT

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

Re: Mapping question. Linking two tables

Wed, 19 Feb 2014 16:11:42 GMT

Hey Sergejs,

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

Thanks

Re: Mapping question. Linking two tables

zekazak — Wed, 19 Feb 2014 16:15:56 GMT

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

Re: Mapping question. Linking two tables

zekazak — Wed, 19 Feb 2014 16:17:58 GMT

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

Re: Mapping question. Linking two tables

Wed, 19 Feb 2014 16:27:08 GMT

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

Re: Mapping question. Linking two tables

zekazak — Wed, 19 Feb 2014 16:48:09 GMT

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

Re: Mapping question. Linking two tables

Wed, 19 Feb 2014 16:54:44 GMT

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

Re: Mapping question. Linking two tables

whiteline — Wed, 19 Feb 2014 19:07:38 GMT

Hi.

I guess that keys are not supposed to have three symbols

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

Re: Mapping question. Linking two tables

maxgro — Wed, 19 Feb 2014 22:04:33 GMT

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

Re: Mapping question. Linking two tables

zekazak — Thu, 20 Feb 2014 11:00:18 GMT

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

Re: Mapping question. Linking two tables

maxgro — Thu, 20 Feb 2014 11:25:26 GMT

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