Joining new records based on existing table

[cawestelaken]

Hello,

I've got a table 'Facts' that I've Concatenate loaded another table onto.
I'd like to join another table with this concatenated table but not the original table.

Original Facts:

Key1	Key2	Country	Name
1	1	NL	Jan
1	1	NL	Piet
1	2	NL	Piet
2	1	NL	Klaas
3	1	NL	Peter
3	2	NL	Peter
4	1	BE	Klaas
5	2	BE	Jan
5	3	BE	Jan
6	2	USA	Piet

To concatenate:

Key1	Key2	Flagged
1	1	Y
2	1	Y
3	1	Y
4	1	Y
5	2	Y
6	2	Y

Concatenated table:

Key1	Key2	Country	Name	Flagged
1	1	NL	Jan
1	1	NL	Piet
1	2	NL	Piet
2	1	NL	Klaas
3	1	NL	Peter
3	2	NL	Peter
4	1	BE	Klaas
5	2	BE	Jan
5	3	BE	Jan
6	2	USA	Piet
1	1			Y
2	1			Y
3	1			Y
4	1			Y
5	2			Y
6	2			Y

I've been trying for a better way to combine the two tables, such as doing a join. Sadly in the full table it is possible for 2 records to completely be the same except for 1 value. (In the example for instance this is "Name") thus this'd result in a record being flagged when it shouldn't be. I've yet to find a better solution, but as far as I am aware there is none. (If you've got any advice, I'll show my ultimately desired result at the bottom of this question.)

To join:

Key1	Key2	Open
1	1	Y
2	1	Y
5	2	Y
6	2	Y

Desired result:

Key1	Key2	Country	Name	Flagged	Open
1	1	NL	Jan
1	1	NL	Piet
1	2	NL	Piet
2	1	NL	Klaas
3	1	NL	Peter
3	2	NL	Peter
4	1	BE	Klaas
5	2	BE	Jan
5	3	BE	Jan
6	2	USA	Piet
1	1			Y	Y
2	1			Y	Y
3	1			Y
4	1			Y
5	2			Y	Y
6	2			Y	Y

My idea was to do so as follows:

Left Join Load
Key1,
Key2,
Open
Resident To Join
Where IsNull(Country);

Sadly this gives me this error:
"Field 'Country' not found"

Now I assume this is because there is no 'Country' in 'To Join'.

Anyone know how to fix this?

Yours sincerely,
Casper Westelaken

Ultimately desired result:

I've tried to achieve this. The second record in the example (1,1,NL,Piet) is what's making this problematic.

Key1	Key2	Country	Name	Flagged	Open
1	1	NL	Jan	Y	Y
1	1	NL	Piet
1	2	NL	Piet
2	1	NL	Klaas	Y	Y
3	1	NL	Peter	Y
3	2	NL	Peter
4	1	BE	Klaas	Y
5	2	BE	Jan	Y	Y
5	3	BE	Jan
6	2	USA	Piet	Y	Y

[OmarBenSalem]

toConcatenate:
load * Inline [
Key1, Key2, Flagged
1, 1, Y
2, 1, Y
3, 1, Y
4, 1, Y
5, 2, Y
6, 2, Y
];

left Join(toConcatenate)
toJoin:
load * Inline [
Key1, Key2, Open
1, 1, Y
2, 1, Y
5, 2, Y
6, 2, Y
];

Concatenate(toConcatenate)
Fact:
load * Inline [
Key1, Key2, Country, Name
1, 1, NL, Jan
1, 1, NL, Piet
1, 2, NL, Piet
2, 1, NL, Klaas
3, 1, NL, Peter
3, 2, NL, Peter
4, 1, BE, Klaas
5, 2, BE, Jan
5, 3, BE, Jan
6, 2, USA, Piet
];

result:

[cawestelaken]

Essentially you're saying that I need to try to load it the other way around?

[OmarBenSalem]

yes