Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
sibijoseph
Contributor II
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.

Labels (1)
1 Reply
micheledenardi
Specialist II
Specialist II

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

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.