Match two tables depending of column and values

[ThibKm]

Morning,

I'm facing an issue that is too hard for my qlik skills.

You'll find below a draw of what i'm looking for.

The most important things is to match red part infos, for "fruits" that cannot always be identified with same name or codification.

Yes, this issue is due to messy data, i'm trying to make an app that would do matching job for us.

Sorry for complexity, i hope there is a way

[Marijn · Accepted solution]

Hi ThibKm,
Do you know in advance which fields will match? Because if it's random, I think you'll have a hard time matching at all. Maybe you can do it like this, but it only works if you're absolutely sure there won't be overlapping values. This script will try to match every record on all the values in the example.

I have not tested the script because I don't have data. There might be typos there.

/* Match all records that have matching names */
Name_table:
load Name as %KEY,
Name as [Name.Table 1],
[CODE A] as [CODE A.Table 1],
[CODE B] as [CODE B.Table 1],
[CODE C] as [CODE C.Table 1],
Text
resident [TABLE 1];

inner join (Name_table)
Name as %KEY,
Name as [Name.Table 2],
[CODE A] as [CODE A.Table 2],
[CODE B] as [CODE B.Table 2],
[CODE C] as [CODE C.Table 2],
Info1
resident [TABLE 2];

drop field %KEY from Name_table;

/* Match all records that have matching CODE A */
CODEA_table:
NOCONCATENATE load
Name as [Name.Table 1],
[CODE A] as %KEY,
[CODE A] as [CODE A.Table 1],
[CODE B] as [CODE B.Table 1],
[CODE C] as [CODE C.Table 1],
Text
resident [TABLE 1];

inner join (CODEA_table)
Name as [Name.Table 2],
[CODE A] as %KEY,
[CODE A] as [CODE A.Table 2],
[CODE B] as [CODE B.Table 2],
[CODE C] as [CODE C.Table 2],
Info1
resident [TABLE 2];

DROP FIELD %KEY FROM CODEA_table;

/* Match all records that have matching CODE B */
CODEB_table:
NOCONCATENATE load
Name as [Name.Table 1],
[CODE B] as %KEY,
[CODE A] as [CODE A.Table 1],
[CODE B] as [CODE B.Table 1],
[CODE C] as [CODE C.Table 1],
Text
resident [TABLE 1];

inner join (CODEB_table)
Name as [Name.Table 2],
[CODE B] as %KEY,
[CODE A] as [CODE A.Table 2],
[CODE B] as [CODE B.Table 2],
[CODE C] as [CODE C.Table 2],
Info1
resident [TABLE 2];

DROP FIELD %KEY FROM CODEB_table;

/* Match all records that have matching CODE C */
CODEC_table:
NOCONCATENATE load
Name as [Name.Table 1],
[CODE C] as %KEY,
[CODE A] as [CODE A.Table 1],
[CODE B] as [CODE B.Table 1],
[CODE C] as [CODE C.Table 1],
Text
resident [TABLE 1];

inner join (CODEC_table)
Name as [Name.Table 2],
[CODE C] as %KEY,
[CODE A] as [CODE A.Table 2],
[CODE B] as [CODE B.Table 2],
[CODE C] as [CODE C.Table 2],
Info1
resident [TABLE 2];

DROP FIELD %KEY FROM CODEC_table;

/* Concatenate all matched records into one table */
main_table:
noconcatenate load * resident Name_table;
concatenate (main_table) load * resident CODEA_table;
concatenate (main_table) load * resident CODEB_table;
concatenate (main_table) load * resident CODEC_table;

/* Add records where none of the values exist in the main table */

concatenate (main_table) load 
Name as [Name.Table 1],
[CODE A] as [CODE A.Table 1],
[CODE B] as [CODE B.Table 1],
[CODE C] as [CODE C.Table 1],
Text
resident [Table 1]
where not exists ([Name.Table 1],[Name]) and
where not exists ([CODE A.Table 1],[CODE A]) and
where not exists ([CODE B.Table 1],[CODE B]) and
where not exists ([CODE C.Table 1],[CODE C]);

concatenate (main_table) load 
Name as [Name.Table 2],
[CODE A] as [CODE A.Table 2],
[CODE B] as [CODE B.Table 2],
[CODE C] as [CODE C.Table 2],
Info1
resident [Table 2]
where not exists ([Name.Table 2],[Name]) and
where not exists ([CODE A.Table 2],[CODE A]) and
where not exists ([CODE B.Table 2],[CODE B]) and
where not exists ([CODE C.Table 2],[CODE C]);

/* Drop all unnecessary tables */
drop tables [TABLE 1],[TABLE 2],Name_table,CODEA_table,CODEB_table,CODEC_table;

[ThibKm]

Thanks Marjin, that's an interresting way to proceed.

My only concerns is indeed that not all values having a match in both table.

My Table 1 is the most complete, and i need to match all values that can be found in Table 2, while Table 2 is not exhaustive, either finding entry by name or code A,B,C.

If i understood well how you proceed on above, it will create a table matching entries found by name or code A,B,C; but will also delete entries from Table 1 that doesn't match any value in Table 2.

But another way to complete with above, is taking back Table 1, then concatenate with main_table all values found => will that work ?

[Marijn]

In the last part I add all record that are not in the main_table yet. The 'where not exists' checks whether one of the values is already in the table, if not it will add the record:

concatenate (main_table) load 
Name as [Name.Table 1],
[CODE A] as [CODE A.Table 1],
[CODE B] as [CODE B.Table 1],
[CODE C] as [CODE C.Table 1],
Text
resident [Table 1]
where not exists ([Name.Table 1],[Name]) and
where not exists ([CODE A.Table 1],[CODE A]) and
where not exists ([CODE B.Table 1],[CODE B]) and
where not exists ([CODE C.Table 1],[CODE C]);

concatenate (main_table) load 
Name as [Name.Table 2],
[CODE A] as [CODE A.Table 2],
[CODE B] as [CODE B.Table 2],
[CODE C] as [CODE C.Table 2],
Info1
resident [Table 2]
where not exists ([Name.Table 2],[Name]) and
where not exists ([CODE A.Table 2],[CODE A]) and
where not exists ([CODE B.Table 2],[CODE B]) and
where not exists ([CODE C.Table 2],[CODE C]);

[ThibKm]

That's great !

Let me test it properly and come back to this discussion.

Thanks again for your help

[Marijn]

Another option would be a left join on table 1, then you keep all records from table 1 and add all available data from table 2. Would be something like this:

/* Match all records that have matching names */
Name_table:
load Name as %KEY,
Name as [Name.Table 1],
[CODE A] as [CODE A.Table 1],
[CODE B] as [CODE B.Table 1],
[CODE C] as [CODE C.Table 1],
Text
resident [TABLE 1];

left join (Name_table)
Name as %KEY,
Name as [Name.Table 2],
[CODE A] as [CODE A.Table 2],
[CODE B] as [CODE B.Table 2],
[CODE C] as [CODE C.Table 2],
Info1
resident [TABLE 2];

drop field %KEY from Name_table;

/* Match all records that have matching CODE A */
CODEA_table:
NOCONCATENATE load
Name as [Name.Table 1],
[CODE A] as %KEY,
[CODE A] as [CODE A.Table 1],
[CODE B] as [CODE B.Table 1],
[CODE C] as [CODE C.Table 1],
Text
resident [TABLE 1];

left join (CODEA_table)
Name as [Name.Table 2],
[CODE A] as %KEY,
[CODE A] as [CODE A.Table 2],
[CODE B] as [CODE B.Table 2],
[CODE C] as [CODE C.Table 2],
Info1
resident [TABLE 2];

DROP FIELD %KEY FROM CODEA_table;

/* Match all records that have matching CODE B */
CODEB_table:
NOCONCATENATE load
Name as [Name.Table 1],
[CODE B] as %KEY,
[CODE A] as [CODE A.Table 1],
[CODE B] as [CODE B.Table 1],
[CODE C] as [CODE C.Table 1],
Text
resident [TABLE 1];

left join (CODEB_table)
Name as [Name.Table 2],
[CODE B] as %KEY,
[CODE A] as [CODE A.Table 2],
[CODE B] as [CODE B.Table 2],
[CODE C] as [CODE C.Table 2],
Info1
resident [TABLE 2];

DROP FIELD %KEY FROM CODEB_table;

/* Match all records that have matching CODE C */
CODEC_table:
NOCONCATENATE load
Name as [Name.Table 1],
[CODE C] as %KEY,
[CODE A] as [CODE A.Table 1],
[CODE B] as [CODE B.Table 1],
[CODE C] as [CODE C.Table 1],
Text
resident [TABLE 1];

left join (CODEC_table)
Name as [Name.Table 2],
[CODE C] as %KEY,
[CODE A] as [CODE A.Table 2],
[CODE B] as [CODE B.Table 2],
[CODE C] as [CODE C.Table 2],
Info1
resident [TABLE 2];

DROP FIELD %KEY FROM CODEC_table;

/* Concatenate all matched records into one table */
main_table_temp:
noconcatenate load * resident Name_table;
concatenate (main_table_temp) load * resident CODEA_table;
concatenate (main_table_temp) load * resident CODEB_table;
concatenate (main_table_temp) load * resident CODEC_table;

/* Drop all unnecessary tables */
drop tables [TABLE 1],[TABLE 2],Name_table,CODEA_table,CODEB_table,CODEC_table;

/* Load distinct to delete any duplicate records */
main_table:
noconcatenate load distinct *
resident main_table_temp;

drop table main_table_temp;

[ThibKm]

That's really great help, thank you.

With this way i'm on the way to find solution for my problem.

One question : should i add "load" after inner join ? Since i got error without it.

Working on it but again, thanks a lot

[Marijn]

Yes you are right, I see I made a mistake. It's always like this:
inner join (table) load
left join (table) load

[ThibKm]

Thanks ! i corrected it.

Also, building Name_Table and CODEA_Table is making Synthetic keys (with field Name.Table1, CodeA.Table1...) between two tables while i want first to have 4 separate Table then merge these.

How do i avoid that ?

[Marijn]

You could use this:

Qualify [CODE A.Table 1] ,[CODE A.Table 2],[CODE B.Table 1] ,[CODE B.Table 2],[CODE C.Table 1] ,[CODE C.Table 2],[Name.Table 1],[Name.Table 2];

Always make sure the field you will use to join it together is unqualified when joining. In my opinion, the synthetic keys shouldn't be a problem if you will concatenate or join the tables together. Because the synthetics key consists of the fields use to join or overlapping fields that will be concatenated. You can use the qualify temporarily when you want to check the results.

See documentation here: https://help.qlik.com/en-US/qlikview/May2022/Subsystems/Client/Content/QV_QlikView/Scripting/ScriptR...