topic Re: Join based on two columns in QlikView

Join based on two columns

qlikmark1990 — Mon, 16 Apr 2018 14:14:33 GMT

I'd like to perform a join of two tables where the join is based on two columns in both tables:

A part of my first table is :

DBCYearEnd	MainGroup
2015	Depression
2015	Depression
2015	Anxiety
2015	Addiction
2016	Addiction
2015	Depression
2015	Addiction
2015	Depression
2015	Depression
2015	Other
2015	Personality disorder
2015	Depression
2015	Anxiety
2015	Depression
2015	Depression
2015	Depression
2015	PTSD
2015	Addiction
2015	Addiction
2015	Depression

and a part of the second table is :

DiagnoseId	Jaar	DeltaT_Landelijk
Addiction	2015	7,6
Anxiety	2015	7,7
Depression	2015	8,4
Other	2015	5
Personality disorder	2015	4,8
PTSD	2015	6,8
Somatoform disorder	2015	8,1
Addiction	2016	8,7
Anxiety	2016	8,4
Depression	2016	8,7
Other	2016	5,7
Personality disorder	2016	5,3
PTSD	2016	7,3
Somatoform disorder	2016	8,5

I'd like to link the field 'DeltaT_Landelijk' to the first table so it will be like this :

DBCYearEnd	MainGroup	DeltaTScoreNationwide
2015	Depression	8,4
2015	Depression	8,4
2015	Anxiety	7,7
2015	Addiction	7,6
2016	Addiction	8,7

DBCYearEnd = Jaar and

DiagnoseId = MainGroup

How do I perform this join?

Re: Join based on two columns

olivierrobin — Mon, 16 Apr 2018 14:26:45 GMT

hello

Qlikview will use tne name of the columns to join your table

you have to rename the columns of 1 table to match the names of the other

or in then join, use the as

left join(Table1)

load

DBCYearEnd as Jaar,

DiagnoseId as MainGroup,

other columns

Re: Join based on two columns

stigchel — Mon, 16 Apr 2018 14:29:38 GMT

Something like this (also attached)

LOAD DBCYearEnd,

MainGroup

FROM

[https://community.qlik.com/thread/298405]

(html, codepage is 1252, embedded labels, table is @1);

Join Load DiagnoseId as MainGroup, Jaar as DBCYearEnd,DeltaT_Landelijk;

LOAD DiagnoseId,

Jaar,

DeltaT_Landelijk

FROM

[https://community.qlik.com/thread/298405]

(html, codepage is 1252, embedded labels, table is @2);

Re: Join based on two columns

passionate — Mon, 16 Apr 2018 14:41:38 GMT

Use Auto number to form a Key and then join using this Key

Re: Join based on two columns

qlikmark1990 — Thu, 19 Apr 2018 12:45:48 GMT

I solved the problem by merging the two fields together into a new field 'DBCMainGroupYearEnd' that contains the diagnoses and the year. I made a temporary table to join to the table 'Treatment'. After this I removed the field 'DBCMainGroupYearEnd' from the table 'Treatment' and dropped the table 'SubTabelTreatment'.

HoofdtabelTreatmentStage:

LOAD *,

DBCMainGroup&'_'&DBCYearEnd as DBCMainGroupYearEnd

Resident Treatment;

Drop Table Treatment;

Rename Table HoofdtabelTreatmentStage to Treatment;

SubTabelTreatment:

LOAD DiagnoseId as DBCMainGroup, Jaar as DBCYearEnd, DeltaT_Landelijk as DeltaTNationwide

FROM [vb DiagnoseScore.xlsx] (ooxml, embedded labels, table is Blad1);

Join(Treatment)

LOAD

DBCMainGroup&'_'&DBCYearEnd as DBCMainGroupYearEnd,

DeltaTNationwide

Resident SubTabelTreatment;

Drop Field DBCMainGroupYearEnd from Treatment;

DROP Table SubTabelTreatment;