Concatenate with null values results in double rows

[leovbelzen]

Hi, 

For most of you a simple question I think, but I have to combine 2 tables (Table 1 and Table 2). Both tables have the same key field (CAS_Klantnaam), however I want to merge them into 2 single table. This works in that way that 1 table is created, but with double rows for the data in table 2. I think this is caused by the null values. Is there a way to workaround this.

Example:

Table 1
TicketNummer	"Cherwell Code"	CAS_Klantnaam
111111	-	Phillips
111222	N2SLE	-
111333	-	Amstel

Table 2
CAS_Klantnaam	"CAS Bedrijfsnummer"
Phillips	AABBCC
Amstel	DDEEFF

Table_1:

LOAD

TicketNummer,
"Cherwell Code",
CAS_Klantnaam

From Table 1

Concatenate (Table_1)

LOAD

CAS_Klantnaam,
"CAS Bedrijfsnummer"

From Table 2

I would expect a result as:

Table 1
Ticketnummer	"Cherwell Code"	CAS_Klantnaam	"CAS Bedrijfsnummer"
111111	-	Phillips	AABBCC
111222	N2SLE	-	-
111333	-	Amstel	DDEEFF

But instead I get:

Table 1
Ticketnummer	"Cherwell Code"	CAS_Klantnaam	"CAS Bedrijfsnummer"
111111	-	Phillips	AABBCC
111111	-	Phillips	-
111222	N2SLE	-	-
111333	-	Amstel	DDEEFF
111333	-	Amstel	-

What am I doing wrong?

[Venkat7]

do join instead of concatenate

[leovbelzen]

this gives the same result.

[Padma123 · Accepted solution]

use join you will get same output

t1:
LOAD
TicketNummer,
["Cherwell Code"],
CAS_Klantnaam
FROM path;
join

t2:
LOAD
CAS_Klantnaam,
["CAS Bedrijfsnummer"]
FROM path;

[leovbelzen]

Sorry, my mistake. This works indeed. There were some datalines with wrong information.

Thanks a lot!

[marcus_sommer]

Make sure that you could identify each record. Therefore I suggest you add:

recno() as RecNo,
rowno() as RowNo,
'Table_1' as Source // respectively Table_2 by the second load

to each load and then add these fields also to the table-box. I assume there is no error with the load itself else the data are different to your expectation.