So I'm one step from finalizing a new application and of course I've gotten stuck.
The problem is that I'm trying to tag a transaction to data from another table but I can't for the life of me figure out how to design the key.
Lets say that this is my input data:
load * inline
[
GeoCountry, GeoState, GeoCity, GeoImportantData
US, CA, Los Angeles, 44455
US, CA, , 99933
US, MA, Boston, 55588
US, MA, , 88811
]
;
load * inline
[
Country, State, City, CustomerID
US, CA, Los Angeles, 1
US, CA, San Francisco, 2
US, CA, San Jose, 3
US, MA, Boston, 4
US, MA, Springfield, 5
]
Now, I want to tag "GeoImportantData" to each customer. The problem is that all customers have a city whereas only some cities have unique "ImportantData".
A person from LA needs to get value "44455" added but his brother in SF should get "99933". I'm sure this is ridiculously simple but I'm completely stuck in a loop (metaphorical, not database-wise...)
Creating a key from "GeoCountry & GeoState" would mean that a person from CA would get two possible ImportantDatas, creating one from GeoCountry & GeoState & GeoCity would mean that a person from a city that does not have its own unique ImportantData is null.
I desperately need your help in figuring out how to solve this.
