Join/merge table with same fields but different data

[Timba69]

Hi,

I'm trying to merge 3 tables. 

With a left join statement the 'Relation' and 'Business' tables are merged. But I can't get the 'Customers' table merged as well.

After the join of the 'Business' table i have now 3 combined fields : REL_ALG_NR_EXTERN, REL_ALG_KD_TITULT and REL_ALG_NM_RELATIE. 

A join with the 'Customers' table now causes extra records with '-' in the fields as the 3 field key makes them all unique.

The unique key should only be the REL_ALG_NR_EXTERN field.

The reason that I want to merge the tables is that I want to replace '-' values with "N" in fields like 'Roken' and '%AO'.

//**************************************************************
//Relation
Relatie:
LOAD PRO2PA AS REL_ALG_NR_EXTERN,
IF(PRO2XBA = 'N', 'P', 'Z') AS REL_ALG_KD_SRT_REL,
PRO2XCA AS REL_ALG_EMAIL,
PRO3ETA AS MAILIND

FROM
[V:\GL_PROSRLP.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

//**************************************************************
//Business 

Left join(Relatie)
LOAD PRO2PA AS REL_ALG_NR_EXTERN,
PRO2XAA AS REL_ALG_NM_RELATIE,
50 AS REL_ALG_KD_TITULT

FROM
[V:\\GL_PROSRPP.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq) ;

//**************************************************************
//Customers

NP:
LOAD PRO2PA AS REL_ALG_NR_EXTERN,
 PRO94A AS REL_PART_NM_ROEP,
 PRO8BA AS REL_ALG_VOORLT,
 PRO8AA AS REL_ALG_VOORVG,
 PRO8CA AS REL_ALG_NM_RELATIE,
 IF(PRO3IA = 'M', 1, 2) AS REL_ALG_KD_TITULT,
 PRO2Y3A AS ROKEN,
 PRO2Y4A AS %AO,
 PRO3IA AS REL_PART_GESL_REL,
 IF(PRO83A = '0001-01-01', '', DATE(PRO83A)) AS REL_PART_DT_GEB,
 PRD61A AS BEROEP,
 IF(PRO2X3A = '0001-01-01', '', DATE(PRO2X3A)) AS REL_PART_DT_OVERL

FROM
[V:\\GL_PROSNPP.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Any suggestions?

[mrtinsjoao]

You can concatenate these tables or join in one more and reproces the tables to do the job

run this

NP:
LOAD PRO2PA AS REL_ALG_NR_EXTERN,
 PRO94A AS REL_PART_NM_ROEP,
 PRO8BA AS REL_ALG_VOORLT,
 PRO8AA AS REL_ALG_VOORVG,
 PRO8CA AS REL_ALG_NM_RELATIE,
 IF(PRO3IA = 'M', 1, 2) AS REL_ALG_KD_TITULT,
 PRO2Y3A AS ROKEN,
 PRO2Y4A AS %AO,
 PRO3IA AS REL_PART_GESL_REL,
 IF(PRO83A = '0001-01-01', '', DATE(PRO83A)) AS REL_PART_DT_GEB,
 PRD61A AS BEROEP,
 IF(PRO2X3A = '0001-01-01', '', DATE(PRO2X3A)) AS REL_PART_DT_OVERL

FROM
[V:\\GL_PROSNPP.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Left join
LOAD PRO2PA AS REL_ALG_NR_EXTERN,
PRO2XAA AS REL_ALG_NM_RELATIE,
50 AS REL_ALG_KD_TITULT
FROM
[V:\\GL_PROSRPP.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq) ;

left join
LOAD PRO2PA AS REL_ALG_NR_EXTERN,
IF(PRO2XBA = 'N', 'P', 'Z') AS REL_ALG_KD_SRT_REL,
PRO2XCA AS REL_ALG_EMAIL,
PRO3ETA AS MAILIND

FROM
[V:\GL_PROSRLP.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

now you have one table and you can reproces to remove the 'null'

😉 

[Timba69]

I dont think that's going to work.

The 'Relation' table consists of all relation records and is the main table.

The 'Business' and 'Customer' table are adding additional info. 

If i'm doing it the other way around i will be missing a lot of records?

[mrtinsjoao]

Use this,

TABLE:
LOAD PRO2PA AS REL_ALG_NR_EXTERN,
IF(PRO2XBA = 'N', 'P', 'Z') AS REL_ALG_KD_SRT_REL,
PRO2XCA AS REL_ALG_EMAIL,
PRO3ETA AS MAILIND

FROM
[V:\GL_PROSRLP.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

left join
LOAD PRO2PA AS REL_ALG_NR_EXTERN,
 PRO94A AS REL_PART_NM_ROEP,
 PRO8BA AS REL_ALG_VOORLT,
 PRO8AA AS REL_ALG_VOORVG,
 PRO8CA AS REL_ALG_NM_RELATIE,
 IF(PRO3IA = 'M', 1, 2) AS REL_ALG_KD_TITULT,
 PRO2Y3A AS ROKEN,
 PRO2Y4A AS %AO,
 PRO3IA AS REL_PART_GESL_REL,
 IF(PRO83A = '0001-01-01', '', DATE(PRO83A)) AS REL_PART_DT_GEB,
 PRD61A AS BEROEP,
 IF(PRO2X3A = '0001-01-01', '', DATE(PRO2X3A)) AS REL_PART_DT_OVERL

FROM
[V:\\GL_PROSNPP.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);

Left join
LOAD PRO2PA AS REL_ALG_NR_EXTERN,
PRO2XAA AS REL_ALG_NM_RELATIE,
50 AS REL_ALG_KD_TITULT
FROM
[V:\\GL_PROSRPP.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq) ;

Now you don't loss any records

[Timba69]

Sorry Mrtinsjaoa. I'm still quite new to QV, so maybe i misunderstand.

But is this not almost the same as I was doing except turning around the 'Business' and the 'Customer' table?

My problem is that if i join the 3 tables like this, the 2nd join will result in empty records cause the 3rd table can't find the 'REL_ALG_NR_EXTERN' & 'REL_ALG_NM_RELATIE' & 'REL_ALG_KD_TITULT'  key.

It works fine with the 1st join cause then the join is only made on the 'REL_ALG_NR_EXTERN' field. My problem is the 2nd join.

 

[mrtinsjoao]

haha, you always learn something new in qlikview.
when you join, you will have just one table.
if you not join, qlikview auto concatenate the tables and the entries that not match will be there.
i think the key that are wrong is "REL_ALG_KD_TITULT" on the 3 table.

[Timba69 · Accepted solution]

Yes haha. 

The main problem is that i need 1 table. I know the auto concatenate provides me with the right data, but i wan't to change '-' fields after the load. 

Both the "REL_ALG_KD_TITULT" and the "REL_ALG_NM_RELATIE" cause the problem. They are unique in combination with the "REL_ALG_NR_EXTERN".

But discussing this issue with you gave me a new idea. 

I will rename the "REL_ALG_KD_TITULT" and the "REL_ALG_NM_RELATIE" into ".. TITULT2" AND "....RELATIE2". This way the load goes smoothly into 1 table. After i can reload the table and move the data back to the original fields.

Would that work?

[mrtinsjoao]

to change de ' - ' per 'N' you can do that
noconcatenate
TEMP:
LOAD
if(isnull(REL_ALG_NR_EXTERN),N, REL_ALG_NR_EXTERN) as REL_ALG_NR_EXTERN,
REL_ALG_KD_SRT_REL,
REL_ALG_EMAIL,
MAILIND,
 REL_PART_NM_ROEP,
 REL_ALG_VOORLT,
 REL_ALG_VOORVG,
 REL_ALG_NM_RELATIE,
REL_ALG_KD_TITULT,
 if(isnull(ROKEN),N,ROKEN) as ROKEN
 if(isnull(%AO),N,%AO) as %AO
 REL_PART_GESL_REL,
REL_PART_DT_GEB,
 PRD61A AS BEROEP,
REL_PART_DT_OVERL
REL_ALG_NM_RELATIE,
REL_ALG_KD_TITULT
Resident ReloadMain;

[Timba69 · Accepted solution]

Yes, that's what i did.

RelatieMain:

NoConcatenate

LOAD

REL_ALG_NR_EXTERN,
REL_ALG_KD_SRT_REL,
REL_ALG_EMAIL,
MAILIND,
IF(ISNULL(REL_ALG_NM_RELATIE), REL_ALG_NM_RELATIE_NP, REL_ALG_NM_RELATIE) AS REL_ALG_NM_RELATIE,
IF(ISNULL(REL_ALG_KD_TITULT), REL_ALG_KD_TITULT_NP, REL_ALG_KD_TITULT) AS REL_ALG_KD_TITULT,
REL_PART_NM_ROEP,
REL_ALG_VOORLT,
REL_ALG_VOORVG,
ROKEN,
%AO,
REL_PART_GESL_REL,
REL_PART_DT_GEB,
BEROEP,
REL_PART_DT_OVERL,
NWSBRF,
INZNVRZ,
UITVMON,
HERKOMST,
APINR,
REL_PART_NR_SOFI,
ADRCAT,
PSTCALG,
REL_ALG_KORADR,
REL_ALG_POSTKD,
REL_ALG_WOONPL,
REL_ALG_KD_LAND,
REL_ALG_NR_TELPRIVE,
REL_ALG_NR_TELZAAK,
REL_ALG_NR_FAX,
REL_ALG_NR_TELAUTO,
ALG_BANK_KD_LAND_1,
ALG_BANK_NR_REKENING_1,
ALG_BANK_NR_BIC_1,
ALG_BANK_NR_IBAN_1,
ALG_BANK_KD_LAND_2,
ALG_BANK_NR_REKENING_2,
ALG_BANK_NR_BIC_2,
ALG_BANK_NR_IBAN_2,
ALG_BANK_KD_LAND_3,
ALG_BANK_NR_REKENING_3,
ALG_BANK_NR_BIC_3,
ALG_BANK_NR_IBAN_3,
ALG_BANK_KD_LAND_4,
ALG_BANK_NR_REKENING_4,
ALG_BANK_NR_BIC_4,
ALG_BANK_NR_IBAN_4,
TP,
CTCTPERS,
BETPL,
VERZNM,
VERZEKERDE,
PANDHDR

Resident Relatie;

DROP Table Relatie;

Thank you for your suggestions and time!