topic Re: Wildcard Lookup between two tables in QlikView

Wildcard Lookup between two tables

nivey2012 — Mon, 11 Jun 2018 17:37:29 GMT

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 Number	Case Number	Title
1234.555USP1	NT-44444	Title 123
5858.4545CAB2	BO-1234	Long Title Description 45
3214.9874USC1	216984	Title 67

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

ID	Description
1	1234.555USP1 NT-44444 Title 123
2	BO-1234 (5858.4545CAB2) Title Description 45
3	Title 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 Number	Case Number	Title	ID
1234.555USP1	NT-44444	Title 123	1
5858.4545CAB2	BO-1234	Long Title Description 45	2
3214.9874USC1	216984	Title 67	3

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

Thanks in advance!

Nic

Re: Wildcard Lookup between two tables

marcus_sommer — Tue, 12 Jun 2018 13:20:36 GMT

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

Re: Wildcard Lookup between two tables

nivey2012 — Thu, 14 Jun 2018 13:07:54 GMT

Thanks, Marcus! This helps!