DataTransformationCRH/Other Errors.Rmd

---
title: "Other errors"
author: "Scary Scarecrow"
date: "5/23/2022"
output:
  html_document:
    theme: lumen
    highlight: tango
    self_contained: true
    toc: true
    toc_depth: 4
    toc_float: true
    css: style.css
---

```{r setup, include=FALSE}
knitr::opts_chunk$set(echo = TRUE)
library(dplyr)
library(DT)
library(tidyr)
library(stringr)

teapot<-function(data){
    datatable(
      data,
  extensions = "Buttons",
  options = list(
    paging = TRUE,
    scrollX = TRUE,
    searching = TRUE,
    ordering = TRUE,
    dom = 'Bfrtip',
    buttons = c('copy', 'csv', 'excel', 'pdf'),
    pageLength = 10,
    lengthMenu = c(3, 5, 10),
    filter = TRUE
  )
)
}

```

## Read files

```{r}
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

```{r}
contacts[contacts$External_Key==contacts$External_Key[duplicated(contacts$External_Key)],] |> 
teapot()
```




### Accounts

#### Accounts

```{r}
accounts[accounts$External_Key %in% accounts$External_Key[duplicated(accounts$External_Key)],] |> 
  teapot()
```


#### Accounts Identity

```{r}
account_identi[account_identi$External_Key %in% account_identi$External_Key[duplicated(account_identi$External_Key)],] |> 
  teapot()
```



#### Accounts Team

```{r}
account_team[account_team$External_Key %in% account_team$External_Key[duplicated(account_team$External_Key)],] |> 
  teapot()
```


#### Accounts Sales

```{r}
account_sales[account_sales$External_Key %in% account_sales$External_Key[duplicated(account_sales$External_Key)],] |> 
  teapot()
```





### Projects


#### Opportunities

```{r}
opportun[opportun$External_Key %in% opportun$External_Key[duplicated(opportun$External_Key)],] |> 
  teapot()
```




#### Opportunity Party

```{r}
opportun_party[opportun_party$External_Key %in% opportun_party$External_Key[duplicated(opportun_party$External_Key)],] |> 
  teapot()
```



#### Opportunity Preceding

```{r}
opportun_prece[opportun_prece$External_Key %in% opportun_prece$External_Key[duplicated(opportun_prece$External_Key)],] |> 
  teapot()
```


#### Opportunity Sales

```{r}
opportun_sales[opportun_sales$External_Key %in% opportun_sales$External_Key[duplicated(opportun_sales$External_Key)],] |> 
  teapot()
```


## Account Vs Contacts

```{r}
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)
```

### Missing from Accounts (Exists in Contacts)

```{r}
op |> 
  filter(Result == "Missing from Accounts") |> 
  teapot()
```

### Unique Accounts

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




### Missing from Contacts (Exists in Accounts)

```{r}
op |> 
  filter(Result == "Missing from Contacts") |> 
  teapot()
```



#### Unique Accounts

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



## Project issues


```{r}
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()
  
```