Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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?
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!
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'
😉
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?
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
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.
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?
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!