Qlik Community

Connectivity & Data Prep

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

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
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