I am responsible for reporting all of North America activity which includes US customers and NON US customers. Im trying to figure out by using 3 different sources the best way to prepare my data to show the FULL NAME country, and then apply a Regional code.
I have three QVDs:
QVD 1 will have City, State, Zip, and Country for all customers, including those outside of north america. The address information is Abbreviated (Countries = US, CA, MX, States = TX,IL,NY) + their shipment activity
QVD 2 will have City, State, Zip, and Country for all customers, including those outside of north america, plus contact information. The address information is the full name (Countries = United States, Canada, Mexico, States = Texas, Illinois, New York). This is the prefered standard the team wants to see.
QVD 3 will have an abbreviated US State and an assignment code. It will say 'TX','MIDWEST REGION'. If it is a non US state, then i default to "Non US" code. This is how we will assign each account to a rep (terriroty based)
I can join all the QVDs together--- The issue i cant seem to figure out is that QVD 3 only contains US locations, where as QVD 1 will have customers not in the US AND accounts where address is not supplied (null countries). So im not sure how to basically say "IF QVD1 COUNTRY = US and QVD1 State = "XX" then QVD3 Assignent Code. IF QVD1 Country <> US, then 'Non US'. IF QVD1 Country is null, then 'Other'.
