How to join datasets without a unique ID

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_id, user_name, user_ip



ip_range_start, ip_range_end, country_code


Needing to end up with the following;


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


    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'


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


    Such as:


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

    THEN (correlated country)

    ELSE Country END 



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