Multiple location to reference location dimension

Question

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

LocationID	Country	State	City
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.

How do you handle city names existing in several states, e.g. Venice? — jarlh, Sep 30 '21 at 09:14
@jarlh I use zip codes in the dimension table. Apologies, left it out as I thought it would not impact the question. — sqlenthusiast, Sep 30 '21 at 09:20
Maybe this helps you: https://stackoverflow.com/questions/60089058/split-string-based-on-position-and-delimiters OR this can also be a helpful approach: https://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns — droebi, Sep 30 '21 at 09:49

score 0 · Accepted Answer · answered Oct 01 '21 at 10:08

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

Multiple location to reference location dimension

1 Answers1