Read files

contacts<-read.csv("./contacts/output/combined/combined.csv")

accounts<-read.csv("./accounts/output/combined/combinedaccount.csv")
account_identi<-read.csv("./accounts/output/combined/combinedaccountidentification.csv")
account_team<-read.csv("./accounts/output/combined/combinedaccountteam.csv")
account_sales<-read.csv("./accounts/output/combined/combinedsalesdata.csv")

opportun<-read.csv("./projects/output/combined/combinedopportunity.csv")
opportun_party<-read.csv("./projects/output/combined/combinedopportunitypartyinfo.csv")
opportun_prece<-read.csv("./projects/output/combined/combinedopportunityprecedingfollo.csv")
opportun_sales<-read.csv("./projects/output/combined/combinedopportunitysalesteampartyin.csv")

Duplicates

Contacts

contacts[contacts$External_Key==contacts$External_Key[duplicated(contacts$External_Key)],] |> 
teapot()

Accounts

Accounts

accounts[accounts$External_Key %in% accounts$External_Key[duplicated(accounts$External_Key)],] |> 
  teapot()

Accounts Identity

account_identi[account_identi$External_Key %in% account_identi$External_Key[duplicated(account_identi$External_Key)],] |> 
  teapot()

Accounts Team

account_team[account_team$External_Key %in% account_team$External_Key[duplicated(account_team$External_Key)],] |> 
  teapot()

Accounts Sales

account_sales[account_sales$External_Key %in% account_sales$External_Key[duplicated(account_sales$External_Key)],] |> 
  teapot()

Projects

Opportunities

opportun[opportun$External_Key %in% opportun$External_Key[duplicated(opportun$External_Key)],] |> 
  teapot()

Opportunity Party

opportun_party[opportun_party$External_Key %in% opportun_party$External_Key[duplicated(opportun_party$External_Key)],] |> 
  teapot()

Opportunity Preceding

opportun_prece[opportun_prece$External_Key %in% opportun_prece$External_Key[duplicated(opportun_prece$External_Key)],] |> 
  teapot()

Opportunity Sales

opportun_sales[opportun_sales$External_Key %in% opportun_sales$External_Key[duplicated(opportun_sales$External_Key)],] |> 
  teapot()

Account Vs Contacts

accou<-accounts |> select(External_Key) |> rename(account_key=External_Key)
op<-contacts |> 
  select(External_Key,Account_External_Key) |> 
  rename(contact_key=External_Key) |> 
  full_join(accou, by=c("Account_External_Key"="account_key"), keep=T) |> 
  mutate(Result= ifelse(is.na(account_key),"Missing from Accounts",
                        ifelse(is.na(Account_External_Key), "Missing from Contacts",
                               "All Right")))
op |> count(Result)
##                  Result    n
## 1             All Right 3368
## 2 Missing from Accounts  344
## 3 Missing from Contacts 5072

Missing from Accounts (Exists in Contacts)

op |> 
  filter(Result == "Missing from Accounts") |> 
  teapot()

Unique Accounts

op |> 
  filter(Result == "Missing from Accounts") |> 
  select(Account_External_Key) |> unique() |> 
  teapot()

Missing from Contacts (Exists in Accounts)

op |> 
  filter(Result == "Missing from Contacts") |> 
  teapot()

Unique Accounts

op |> 
  filter(Result == "Missing from Contacts") |> 
  select(account_key) |> unique() |> 
  teapot()

Project issues

opportun |> 
  mutate(grp.var=External_Key) |> 
  select(grp.var, External_Key) |> 
  mutate(grp.var=ifelse(str_detect(grp.var,"-"),str_sub(grp.var,1,str_length(grp.var)-4),grp.var)) |> 
  group_by(grp.var) |> 
  mutate(count=n()) |> 
  filter(count==1) |> 
  rename(source=grp.var) |> 
  ungroup() |> 
  mutate(project_type=ifelse(source==External_Key,"Main","Sub")) |> 
  teapot()
## Warning in instance$preRenderHook(instance): It seems your data is too big
## for client-side DataTables. You may consider server-side processing: https://
## rstudio.github.io/DT/server.html