topic Re: Many to one table join in QlikView

Many to one table join

Thu, 22 Jan 2015 12:04:46 GMT

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.

Re: Many to one table join

PrashantSangle — Thu, 22 Jan 2015 12:11:07 GMT

Hi,

Simple Left join or join will work for you.

Simply load both table due to association relation is created between Consumption and Site

Then use Stright table and those field.

Regards

Re: Many to one table join

vikasmahajan — Thu, 22 Jan 2015 12:16:08 GMT

PFA uses Right join

Vikas

Re: Many to one table join

Anonymous — Thu, 22 Jan 2015 12:19:42 GMT

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

Re: Many to one table join

its_anandrjs — Thu, 22 Jan 2015 12:35:17 GMT

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

Re: Many to one table join

Tue, 10 Feb 2015 09:38:12 GMT

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!