community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Can we geocode IP address in Alteryx based on a range using >= and <=

Atom

Hi,

We just got Alteryx and are loving it. I'm trying to geocode the IP address in my dataset using this free IP address database here: https://lite.ip2location.com/database/ip-country-region-city-latitude-longitude-zipcode-timezone

 

Prior to Alteryx, I would have easily done this in Tableau 2018.1 based on this Tableau knowledge base info here: https://kb.tableau.com/articles/howto/mapping-ip-address-geocode-data

and also in the attached Tableau file.

I am now wondering if this is possible in Alteryx? Can we actually geocode IP addresses in Alteryx

Is it possible to do a join based on a range i.e. >= or <= in Alteryx similar like in Tableau? Does Alteryx join only with the equality sign?

I would so much appreciate your help on this.

 

Thank you very much!

 

Thanks

Atom

Hello Alteryx community,

I am updating this post with clearer details on how Tableau converts the IP addresses in my dataset to numeric format and then joins that using the IP address range.

 

  1. Get the IP addresses in my dataset and do a left join with the IP start and IP end ranges from the database.
  2. Tableau basically converts my IP address to numeric format using the equation in orange below and joins it with the IP start and IP end ranges, respectively using the formula below:
  3. Note that the IP address in my dataset is now joined not just with equals sign but with >= for IP start range and <= for IP end range
  4. I would imagine that Alteryx should be able to do this but I don’t know how???

 

IP START RANGE left join formula.

(INT(

                LEFT([IP Address],(FIND([IP Address], ".")))

                ))*16777216+

                (INT(

                LEFT(

                MID([IP Address],

                FIND([IP Address],".")+1),

                FIND(MID([IP Address],FIND([IP Address],".")+1),".")-1)

                ))*65536+

                (INT(

                MID([IP Address],

                   FIND([IP Address], ".", FIND([IP Address], ".") +1)+1,

                   (FIND([IP Address], ".", FIND([IP Address], ".", FIND([IP Address],"." ) +1) +1)) - (FIND([IP Address], ".", FIND([IP Address], ".") +1))-1

                )

                ))*256+

                (INT(

                RIGHT([IP Address],

                   LEN([IP Address]) -

    FIND([IP Address], ".", FIND([IP Address], ".", FIND([IP Address],"." ) +1) +1)

                )

                ))

 

IP END RANGE

(INT(

                LEFT([IP Address],(FIND([IP Address], ".")))

                ))*16777216+

                (INT(

                LEFT(

                MID([IP Address],

                FIND([IP Address],".")+1),

                FIND(MID([IP Address],FIND([IP Address],".")+1),".")-1)

                ))*65536+

                (INT(

                MID([IP Address],

                   FIND([IP Address], ".", FIND([IP Address], ".") +1)+1,

                   (FIND([IP Address], ".", FIND([IP Address], ".", FIND([IP Address],"." ) +1) +1)) - (FIND([IP Address], ".", FIND([IP Address], ".") +1))-1

                )

                ))*256+

                (INT(

                RIGHT([IP Address],

                   LEN([IP Address]) -

    FIND([IP Address], ".", FIND([IP Address], ".", FIND([IP Address],"." ) +1) +1)

                )

                )) 

I would so much appreciate if you can help me figure out how to do this in Alteryx.

 

This works ok in Tableau when the dataset is small. However, with increased dataset, it's taking FOREVER to create the tableau extract.

If I can do this in Alteryx and create the extract in Alteryx that will be fantastic!

Looking forward to hearing from you.

 

Thanks a million in advance for your help ,

Charles

Labels