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: 
nivey2012
Partner - Contributor
Partner - Contributor

Wildcard Lookup between two tables

   Hello,

I have two sets of data, and looking to Wildcard match between the two tables based on three conditions. The first table has three fields that make up the column in the second table. These three fields can show up in any order with potential additional information in the key field of the second table.

For example, the below table shows the first table with the three fields:

   

Ref NumberCase NumberTitle
1234.555USP1NT-44444Title 123
5858.4545CAB2BO-1234Long Title Description 45
3214.9874USC1216984Title 67

The second table has the Description field which has the combination of the three columns above.

  

IDDescription
11234.555USP1 NT-44444 Title 123
2BO-1234 (5858.4545CAB2) Title Description 45
3Title 67 3214.9874USC1

Now I want to be able to retrieve the ID when the three fields show up in some sort of combination like below.

    

Ref NumberCase NumberTitleID
1234.555USP1NT-44444Title 1231
5858.4545CAB2BO-1234Long Title Description 452
3214.9874USC1216984Title 673

Notes: None of the fields are unique on their own. It's the combination that makes them unique.

Thanks in advance!

Nic

1 Solution

Accepted Solutions
marcus_sommer

I think you need a mapping approach to solve your task whereby I'm not sure that I understood all your requirements right respectively that you mentioned all implications. The following could be probably a bit more elegant but it showed the various steps quite well and of course you could choose other chars as delimiters which are quite unlikely within the data itself and at least it will be good starting point to adjust and/or extend the logic:

t1:
LOAD [Ref Number], [Case Number], [Title]
FROM [https://community.qlik.com/thread/304479] (html, codepage is 1252, embedded labels, table is @1);

m1:
mapping load [Ref Number], [Ref Number] & chr(5000) & chr(5001) resident t1;
m2:
mapping load [Case Number], [Case Number] & chr(5000) & chr(5002) resident t1;
m3:
mapping load [Title], [Title] & chr(5000) & chr(5003) resident t1;

t2:
load *, purgechar(subfield(replace(replace([Description], [Ref Number], ''), [Case Number], ''), chr(5000) & chr(5003), 1), chr(5000) & chr(5001) & chr(5002)) as [Title];
load *, purgechar(subfield(replace([Description], [Ref Number], ''), chr(5000) & chr(5002), 1), chr(5000) & chr(5001)) as [Case Number];
load *, subfield([Description], chr(5000) & chr(5001), 1) as [Ref Number];
load *, purgechar([Description], chr(5000) & chr(5001)& chr(5002) & chr(5003)) as [DescriptionOriginal]where substringcount([Description], chr(5000)) = 3;
load [ID], mapsubstring('m3', [Description]) as [Description];
load [ID], mapsubstring('m2', [Description]) as [Description];
load [ID], mapsubstring('m1', [Description]) as [Description];
LOAD [ID], [Description]
FROM [https://community.qlik.com/thread/304479] (html, codepage is 1252, embedded labels, table is @2);

drop tables t1;

The other records doesn't fullfil the requirement of finding all 3 field-values within the Description.

- Marcus

View solution in original post

2 Replies
marcus_sommer

I think you need a mapping approach to solve your task whereby I'm not sure that I understood all your requirements right respectively that you mentioned all implications. The following could be probably a bit more elegant but it showed the various steps quite well and of course you could choose other chars as delimiters which are quite unlikely within the data itself and at least it will be good starting point to adjust and/or extend the logic:

t1:
LOAD [Ref Number], [Case Number], [Title]
FROM [https://community.qlik.com/thread/304479] (html, codepage is 1252, embedded labels, table is @1);

m1:
mapping load [Ref Number], [Ref Number] & chr(5000) & chr(5001) resident t1;
m2:
mapping load [Case Number], [Case Number] & chr(5000) & chr(5002) resident t1;
m3:
mapping load [Title], [Title] & chr(5000) & chr(5003) resident t1;

t2:
load *, purgechar(subfield(replace(replace([Description], [Ref Number], ''), [Case Number], ''), chr(5000) & chr(5003), 1), chr(5000) & chr(5001) & chr(5002)) as [Title];
load *, purgechar(subfield(replace([Description], [Ref Number], ''), chr(5000) & chr(5002), 1), chr(5000) & chr(5001)) as [Case Number];
load *, subfield([Description], chr(5000) & chr(5001), 1) as [Ref Number];
load *, purgechar([Description], chr(5000) & chr(5001)& chr(5002) & chr(5003)) as [DescriptionOriginal]where substringcount([Description], chr(5000)) = 3;
load [ID], mapsubstring('m3', [Description]) as [Description];
load [ID], mapsubstring('m2', [Description]) as [Description];
load [ID], mapsubstring('m1', [Description]) as [Description];
LOAD [ID], [Description]
FROM [https://community.qlik.com/thread/304479] (html, codepage is 1252, embedded labels, table is @2);

drop tables t1;

The other records doesn't fullfil the requirement of finding all 3 field-values within the Description.

- Marcus

nivey2012
Partner - Contributor
Partner - Contributor
Author

Thanks, Marcus! This helps!