Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@Kain_F can you share a sample data and the expected output ?
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
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 :
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"))