topic Re: JOINS MULTIPLE FIELDS IN MULTIPLE TABLES in Connectivity & Data Prep

JOINS MULTIPLE FIELDS IN MULTIPLE TABLES

Kain_F — Fri, 10 Dec 2021 21:09:00 GMT

Hi all,

I have a datamodel as such

Table_1:

Load

FROM [SOURCE_1.qvd] (qvd);

Table_2:

Load

FROM[SOURCE_2.qvd] (qvd);

TABLE_3:

Load

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.

Re: JOINS MULTIPLE FIELDS IN MULTIPLE TABLES

Taoufiq_Zarra — Thu, 26 Aug 2021 08:26:45 GMT

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

Re: JOINS MULTIPLE FIELDS IN MULTIPLE TABLES

Kain_F — Thu, 26 Aug 2021 09:57:34 GMT

@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

Re: JOINS MULTIPLE FIELDS IN MULTIPLE TABLES

Kain_F — Thu, 26 Aug 2021 10:07:26 GMT

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

Re: JOINS MULTIPLE FIELDS IN MULTIPLE TABLES

Kain_F — Fri, 27 Aug 2021 20:43:34 GMT

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