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:
select distinct loc.TransID, dim_FC.LocationID as from_city_id, dim_LC.LocationID as to_city_id from trans_location loc
INNER JOIN dim_location dim_FC ON dim_FC.City = substring(loc.[From City], 1, charindex('/',loc.[From City])-1)
INNER JOIN dim_location dim_LC ON dim_LC.City = reverse(substring(reverse(loc.[To City]),1,charindex('/',REVERSE(loc.[To City]))-1))
order by loc.TransID
