How to join datasets without a unique ID

MattHunt
MattHunt Member
in Magic ETL

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

Best Answer

  • Godiepi
    Godiepi Coach
    Answer ✓

    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`

     

    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'

Answers

  • Cartergan
    Cartergan Contributor

    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
    Godiepi Coach
    Answer ✓

    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`

     

    Domo Arigato!

    **Say 'Thanks' by clicking the thumbs up in the post that helped you.
    **Please mark the post that solves your problem as 'Accepted Solution'
  • MattHunt
    MattHunt Member

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

Categories