Many-to-one Join | Not able to Join two tables in a specific format

[sibijoseph]

I’m looking to combine 2 tables together Consumption and Site.

The id that I want to combine them with is MPRN however Site will only contain one instance of each MPRN value whereas Consumption will have many.

Example:

Consumption:

MPRN	Consumption	Date
123	80	01/01/2015
123	90	01/12/2014
125	110	21/07/2012
125	120	08/12/2014
125	115	01/10/2014

Site:

MPRN	Name	Active
123	ABC	Y
125	DEF	Y

To be merged into:

MPRN	Consumption	Date	Name	Active
123	80	01/01/2015	ABC	Y
123	90	01/12/2014
125	110	21/07/2012	DEF	Y
125	120	08/12/2014
125	115	01/10/2014

Does anyone know how to achieve this?

Any help is much appreciated.

[micheledenardi]

This works:

Consumption:
Load
MPRN,
Consumption,
Date,
RecNo() as RecordNo
Inline [
MPRN,Consumption,Date
123,80,01/01/2015
123,90,01/12/2014
125,110,21/07/2012
125,120,08/12/2014
125,115,01/10/2014
];

This Works:

FinalTable:
Load * where not Exists(MPRN_Check);
Load
MPRN,
MPRN as MPRN_Check,
Consumption,
Date,
RecordNo as RecordNoCheck
Resident Consumption;
Drop Field MPRN_Check;

Left join(FinalTable)
Load * Inline [
MPRN,Name,Active
123,ABC,Y
125,DEF,Y
];

Concatenate(FinalTable)
Load
*
Resident Consumption
Where not Exists(RecordNoCheck,RecordNo);

Drop Table Consumption;

Drop fields RecordNoCheck,RecordNo;

And the result is: