Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thanks, Marcus! This helps!