Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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 |
356 | 100 | 01/12/2014 |
Site
MPRN | Name | Active |
|
|
123 | ABC | Y |
|
|
To be merged into:
MPRN | Name | Active | Consumption | Date |
123 | ABC | Y | 80 | 01/01/2015 |
123 | ABC | Y | 90 | 01/12/2014 |
Does anyone know how to achieve this?
Any help is much appreciated.
PFA uses Right join
Vikas
Hi,
Simple Left join or join will work for you.
Or
Simply load both table due to association relation is created between Consumption and Site
Then use Stright table and those field.
Regards
PFA uses Right join
Vikas
Hi Ashley,
Try below code:
Consumption:
Load * inline
[
MPRN,Consumption,Date
123,80,01/01/2015
123,90,01/12/2014
356,100,01/12/2014
];
Site:
Load * inline
[
MPRN,Name,Active
123,ABC,Y
];
NoConcatenate
SiteConsumption:
LOAD *
Resident Site;
left Join
LOAD *
Resident Consumption;
drop table Consumption;
drop table Site;
Regards
Neetha
Hi,
Do simple Right join and why not do only right join rather than load resident tables because it increase the load time as well.
Ex:-
Consumption:
Load * inline
[
MPRN,Consumption,Date
123,80,01/01/2015
123,90,01/12/2014
356,100,01/12/2014
];
Site:
Right Join
Load * inline
[
MPRN,Name,Active
123,ABC,Y
];
RENAME Table Consumption to FinalTable;
Regards
Anand
Thanks for the help everyone - right join worked. The reason it wasn't working correcting was because the MPRN field on Site had whitespace before the value!