Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
sibijoseph
New Contributor II

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

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.

1 Reply
micheledenardi
Valued Contributor

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

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:

2019-05-14 16_25_38-QlikView x64 - Copia del rivenditore - [M__test.qvw_].png