Qlik Community

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
Kain_F
Contributor II
Contributor II

JOINS MULTIPLE FIELDS IN MULTIPLE TABLES

Hi all,

 

I have a datamodel as such

Table_1:

Load

A,

B,

C,

FROM [SOURCE_1.qvd] (qvd);

 

Table_2:

Load

D,

E,

F

FROM[SOURCE_2.qvd] (qvd);

 

TABLE_3:

Load

A,

D,

G

FROM[SOURCE_3.qvd] (qvd);

 

What I need to do now is create a link from table 1 to table 3 based on field A and a link from table 2 to table 3 based on field 3.

 

Labels (2)
4 Replies
Taoufiq_Zarra

@Kain_F  can you share a sample data and the expected output ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Kain_F
Contributor II
Contributor II
Author

@Taoufiq_Zarra 

I have the following data:

Table_1 :

load * inline [

Province; Sales; Salesperson

Antwerp;150;Karl

Limburg;200;Mathilde

] (delimiters is ';');

Table_2:

load * inline [

Organisation;Sales;Year

Microsoft;20;2021

IBM;60;2021

] (delimiter is ';');

Table_3:

load * inline [

Organisation;Province;Contact

Microsoft;Antwerp;xxxx.yyyy@microsoft.com

IBM;Brussels;xxxx.yyyy@IBM.com

Apple;Limburg;xxxx.yyyy@apple.com

] (delimiter is ';');

 

My desired output is :

Province   Organisation    Sales    Contact

Antwerp    Microsoft             20          xxxx.yyyy@microsoft.com

Brussels     IBM                        60          xxxx.yyyy@IBM.com

Limburg      Apple                    -           xxxx.yyyy@apple.com

Antwerp          -                       150                             -

Limburg           -                       200                             -

 

When I try to do use an outer join to connect:

 

Table_4:

join table_3 on table_1. Province = table_3.Province

Table_5:

join table_4 on table_2.Organisation = table_4.Organisation

 

I dont get the desired output

Kain_F
Contributor II
Contributor II
Author

@Taoufiq_Zarra 

If I try :

 

table_1 : 

load * inline [...] (delimiter is ';');

concatenate 

table_3:

load * inline [...] (delimiter is ';');

join

table_2 :

load *,inline [....] (delimiter is ';');

 

I get :

 

Kain_F_0-1629972435924.png

 

Kain_F
Contributor II
Contributor II
Author

So this is what I want in R

TABEL1 = TABEL1 %>%
rename_with(toupper)
TABEL2 = TABEL2 %>%
rename_with(toupper)
TABEL3 = TABEL3 %>%
rename(Organisation = Organisation_2) %>%
rename(Sales = `[Table_2.Sales]`) %>%
rename(Year = `[Table_2.Year]`) %>%
rename_with(toupper)

Tabel4 = TABEL1 %>%
full_join(TABEL3,by = c("ORGANISATION" = "ORGANISATION")) %>%
select(!YEAR)

Tabel4 = Tabel4 %>%
mutate(SALESPERSON = '') %>%
select(c("PROVINCE","SALES","SALESPERSON"))

Tabel_5 = union(Tabel4,TABEL2)

Tabel_6 = Tabel_5 %>%
right_join(TABEL1,by = c("PROVINCE" = "PROVINCE"))

 

Kain_F_0-1630097005267.png