JOINS MULTIPLE FIELDS IN MULTIPLE TABLES

[Kain_F]

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.

[Taoufiq_Zarra]

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

[Kain_F]

@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]

@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]

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