How to join datasets without a unique ID

MattHunt

Hey All,

 

Running into a problem, I have a IP address for a user, and wanting to reference the country of origin for IP address. I have imported a IP address database, which shows the IP start and IP end ranges for specific countries. I have modified these and the user IP address to numerical value. The plan was to use between to find the country.

 

However, I am running into an issue joining to data to be able to use the data. Visual example of the tables below;

 

user_ip_access

user_id, user_name, user_ip

 

ip_address_country

ip_range_start, ip_range_end, country_code

 

Needing to end up with the following;

user_ip_access_plus_country

user_id, user_name, user_ip, country_code

Godiepi

Have you try building a dataflow SQL in Domo doing something like this below

 

Select 
concat(a.`user_ip_access`, '_' ,b.`ip_address_country`) as 'user_ip_access_plus_country'
, a.`user_id`
, a.`user_name`
, a.`user_ip`
, b.`country_code`
From `Table1` as a
Left outer join `Table2` as b 
           on a.`user_ip_acces` between b.`ip_range_start` and b.`ip_range_end`

 

Cartergan

Are you able to make it work with a Beast Mode CASE WHEN statement?

 

Such as:

CASE WHEN 

user_ip_access BETWEEN ******** and **********

THEN (correlated country)

ELSE Country END 

 

 

Godiepi

Have you try building a dataflow SQL in Domo doing something like this below

 

Select 
concat(a.`user_ip_access`, '_' ,b.`ip_address_country`) as 'user_ip_access_plus_country'
, a.`user_id`
, a.`user_name`
, a.`user_ip`
, b.`country_code`
From `Table1` as a
Left outer join `Table2` as b 
           on a.`user_ip_acces` between b.`ip_range_start` and b.`ip_range_end`

 

MattHunt

Thanks @Godiepi working perfectly. I had something close, but was not working.