Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikmark1990
Contributor III
Contributor III

Join based on two columns

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 :

   

DBCYearEndMainGroupDeltaTScoreNationwide
2015Depression8,4
2015Depression8,4
2015Anxiety7,7
2015Addiction7,6
2016Addiction8,7

DBCYearEnd = Jaar and

DiagnoseId = MainGroup

How do I perform this join?

1 Solution

Accepted Solutions
qlikmark1990
Contributor III
Contributor III
Author

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;

View solution in original post

4 Replies
olivierrobin
Specialist III
Specialist III

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

stigchel
Partner - Master
Partner - Master

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);

passionate
Specialist
Specialist

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

qlikmark1990
Contributor III
Contributor III
Author

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;