Looking for matching records between two tables

[#1]

Hi All,

I'm currently trying to implement a solution to check records between 4 different bank statements (from different banks) and a financial master database statement. There is a lack of consistency between records however, and no standard unique reference number present in the reports produced by the master database, with records instead defined by a LineDescription field, e.g. 'SCOTT'S PIZZERIA DDR BGC'. The name in this LineDescription is the same as the name in the NAME field in the bank statements. Apart from this there is nothing linking the data tables.

What I want to do is wildmatch the NAME from the bank statement tables with the LineDescription fields and have the records linked in this way.

Is this possible? What is the best way of proceeding?

Many Thanks,

Qlik User

[hector_munoz]

Hi Qlik User,

If I have not missunderstood what you are asking is to join tables among text fields... This is possible but it is recommendable to do by number. For this, you can use Autonumberhash functions.

Regards,

H

[marksmunich]

If i were in you situation, I would try to cleanse the data first and save into a QVD file before proceeding further. this would make your task easier. Though i didnot clearly understand your problem. incase if you have common fields in tables and want to check the matching records, as per qlikview there needs to be a atleast a common field between the tables.

[#4]

Hi Munoz,

I think you're thinking along the right lines. Let me give an example of such a record.

Financial Master Database Statement Table:

LineDescription = 'SCOTT'S PIZZERIA BGC 53042043 24/02'

Bank Statement Table:

NAME='SCOTT'S PIZZERIA'

The contents of the NAME field will always be included in the LineDescription with no anomalies, as the bank statements feed this main system. I want to flag it where the value is present in more than one table.

Kind Regards,

Qlik User

[hector_munoz]

How many records are in each table?

[#6]

Not a huge volume. The maximum that will occur in a month is around 4000 in the master table and 1000 in each of the bank statement tables.

[hector_munoz]

Hi Qlik User,

I attach a sample with some joins that will make something similar than what you need.

In this example I have two tables:

T1 with footbal teams

T2 with footbal players

They share a field Description that is similar but not exactly the same. I have make a join to T1 using the first word in T2 Description and later I have cleared the result. Th precondition is that T2 Description is a left substring of T1 description.

Hope it serves...

Regards,

H

[#8]

Thanks Munoz, this looks like what I'm looking for - I don't however have a commercial version of QlikView and have ran out of the number of applications I can open. Are you able to produce as a .qvf or similarly send the sample data along with the load script?

Thoroughly appreciate the time and effort, and love the example!

[hector_munoz]

This is the script:

T1_AUX:

LOAD * INLINE [

T1 ID, T1 Description

1, Barcelona FC

2, Real Madrid Club de Futbol

3, Real Betis Balompie

4, Atletico de Madrid

5, Athletic de Bilbao

];

T1_AUX2:

NOCONCATENATE LOAD [T1 ID] AS [T1 ID],

  [T1 Description] AS [T1 Description],

  SubField([T1 Description], ' ', 1) AS [T1 Key]

RESIDENT T1_AUX;

DROP TABLE T1_AUX;

T2_AUX:

LOAD * INLINE [

T2 ID, T2 Description, T2 Player

1, Barcelona, Messi

2, Barcelona, Neymar

3, Barcelona, Suarez

4, Barcelona, Iniesta

5, Real Madrid, Bale

6, Real Madrid, Ronaldo

7, Real Madrid, Isco

8, Real Betis, Joaquin

9, Real Betis, Martinez

10, Real Betis, Perez

11, Real Betis, Moya

12, Real Betis, Lopez

13, Real Betis, Antunez

14, Atletico de Madrid, Koke

15, Atletico de Madrid, Gabi

16, Atletico de Madrid, Filipe Luis

17, Atletico de Madrid, Oblak

18, Atletico de Madrid, Godin

19, Athletic de Bilbao, Yeray

20, Athletic de Bilbao, Aduriz

];

T2:

NOCONCATENATE LOAD [T2 ID] AS [T2 ID],

  [T2 Description] AS [T2 Description],

  [T2 Player] AS [T2 Player],

  SubField([T2 Description], ' ', 1) AS [T2 Key]

RESIDENT T2_AUX;

DROP TABLE T2_AUX;

LEFT JOIN (T1_AUX2)

LOAD [T2 Key] AS [T1 Key],

  [T2 Description] AS [T1 Temp Description]

RESIDENT T2;

T1:

NOCONCATENATE LOAD [T1 ID] AS [T1 ID],

  [T1 Description] AS [T1 Description],

  [T1 Temp Description] AS [T2 Description]

RESIDENT T1_AUX2

WHERE SubStringCount([T1 Description], [T1 Temp Description]) > 0;

DROP TABLE T1_AUX2;

Regards,

H

[OmarBenSalem]

Here's how I did:

"Financial Master Database Statement Table":

load *, SubField(LineDescription,' ',1)&SubField(LineDescription,' ',2) as key;

Load * Inline [

LineDescription

SCOTTS PIZZERIA BGC 53042043 24/02

SCOTTS PIZZERIA BGC 53042043 24/03

SCOTTS PIZZERIA BGC 53042043 24/04

Omar Ben Salem et compagnie

Omar Ben Salem et qlik

];

"Bank Statement Table":

load *, SubField(NAME,' ',1)&SubField(NAME,' ',2) as key;

load * Inline [

NAME

SCOTTS PIZZERIA

Omar Ben Salem

];

Result: