Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
Have you tried applymap() function ?
Thanks
chiru.
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
Hey Sergejs,
You can use Purgechar and then just join them. Please check out the attached file.
Thanks
AJ
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
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
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
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
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
Hi.
I guess that keys are not supposed to have three symbols
So what you want to do if there is some ambiguity ?