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