How to join datasets without a unique ID

MattHunt · June 2019

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 · June 2019

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 · June 2019

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 · June 2019

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 · June 2019

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

Magic ETL

Magic ETL

How to join datasets without a unique ID

Welcome!

Best Answer

Answers

Welcome!

Welcome!

Quick Links

Categories