Skip to content
Advertisement

Multiple location to reference location dimension

I am trying to map values associated to my location dimension – dimension seen below:

LocationID Country State City Zip Code Longitude Latitude
1 USA NY Manhattan
2 USA NY Yonkers
3 USA NY Buffalo

I am receiving transaction data where multiple regions may be identified as a value. For example, I will get a value for city as such: Yonkers/Manhattan and Manhattan/Yonkers.

Transaction Data example:

TransID From City To City
1 Yonkers/Manhattan Manhattan/Yonkers
2 Manhattan/Yonkers Yonkers/Buffalo

The rule is that the first city in the ‘From City’ should be used when linking to the location dimension and the last city in the ‘To City’ should be used when linking to the location dimension. The final result should be as followed:

TransID From City ID To City ID
1 2 2
2 1 3

I would really appreciate any assistance on this.

Advertisement

Answer

I tried and got the required results by using inner join on the dimension table and transaction table. Use substring to join first city and last city in ON condition.

Query:

enter image description here

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement