IP address to country & City mapping

[poojashribanger]

Hi All,

I have a table that contains IP addresses and currencies, but I need to determine the country.

Is it possible to map the country using the IP address and currency?

Any Help will be appreciated!

Thanks in Advance!

Opens in new window

PDF Download

Word Download

Excel Download

PowerPoint Download

Document Download

[Or]

If you Google it up, you'll find plenty of options for getting locations from an IP address. Pick one you like and use that.

[poojashribanger]

For IPv4 it's working fine but for IPv6 i am unable to convert to number.

If someone can help me in converting this ipv6 code to number in qlik.

Please see below.
Mapping File range :

My data:

Thanks in Advance

[poojashribanger]

Is there any solution for this?
I am still unable to find solution to convert IPv6 to number in qlik

[lennart_mo]

Hi @poojashribanger,

you can convert numbers from hex to decimal by using following command:

num(num#(NewIPAddress, '(hex)'))

I'm not sure how the ranges from your Mapping Range were created but you'll need to transform each 4 digits. Since you seem to always have the first 4 blocks of the IP, I would suggest something like so:

 num(num#(SubField(NewIPAddress, ':',1), '(hex)')*pow(16, 12))
+num(num#(SubField(NewIPAddress, ':',1), '(hex)')*pow(16,  8))
+num(num#(SubField(NewIPAddress, ':',2), '(hex)')*pow(16,  4))
+num(num#(SubField(NewIPAddress, ':',3), '(hex)')*pow(16,  0))
    as NewIPAddress

[poojashribanger]

Hi @lennart_mo ,

Thanks a lot for suggesting the solution!

I've implemented the IPv6-to-numeric conversion in SQL as below. While Qlik doesn’t support the full precision of the numeric result (it displays the value in exponential format), when I cast it as text, the output appears correctly.

Here’s the core of my SQL logic:

select *,
cast(IPNumtest as char) as test
from ( SELECT *,
CASE

WHEN ip_type = 'IPv4' THEN INET_ATON(NewIPAddress)
ELSE
CAST(
CONV(SUBSTRING(HEX(INET6_ATON(FullIPv6)), 1, 16), 16, 10) AS DECIMAL(39, 0)
) * POW(2, 64) +
CAST(
CONV(SUBSTRING(HEX(INET6_ATON(FullIPv6)), 17, 16), 16, 10) AS DECIMAL(39, 0)
)
END AS IpNumeric,
(CAST(CONV(SUBSTR(HEX(INET6_ATON(FullIPv6)), 1, 16), 16, 10) AS DECIMAL(39,0))
* 18446744073709551616)
+ CAST(CONV(SUBSTR(HEX(INET6_ATON(FullIPv6)), 17, 16), 16, 10) AS DECIMAL(39,0)) as IPNumtest
FROM (
SELECT *,
-- Create FullIPv6 (8 blocks of 4 digits)
CONCAT(
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(NewIPv6_Patched, ':', 1), ':', -1), 4, '0'), ':',
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(NewIPv6_Patched, ':', 2), ':', -1), 4, '0'), ':',
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(NewIPv6_Patched, ':', 3), ':', -1), 4, '0'), ':',
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(NewIPv6_Patched, ':', 4), ':', -1), 4, '0'), ':',
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(NewIPv6_Patched, ':', 5), ':', -1), 4, '0'), ':',
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(NewIPv6_Patched, ':', 6), ':', -1), 4, '0'), ':',
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(NewIPv6_Patched, ':', 7), ':', -1), 4, '0'), ':',
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(NewIPv6_Patched, ':', 8), ':', -1), 4, '0')
) AS FullIPv6
FROM (
SELECT *,
CASE WHEN LEFT(IPaddress, 1) = ',' THEN SUBSTRING(IPaddress, 2) ELSE IPaddress END AS NewIP,
TRIM(SUBSTRING_INDEX(IPaddress, ',', 1)) AS NewIPAddress,
CASE WHEN INSTR(TRIM(SUBSTRING_INDEX(IPaddress, ',', 1)), ':') > 0 THEN 'IPv6' ELSE 'IPv4' END AS ip_type,
CASE
WHEN INSTR(TRIM(SUBSTRING_INDEX(IPaddress, ',', 1)), ':') = 0
THEN TRIM(SUBSTRING_INDEX(IPaddress, ',', 1))
END AS IpAddress_ipv4,
CASE
WHEN (LENGTH(TRIM(SUBSTRING_INDEX(IPaddress, ',', 1))) -
LENGTH(REPLACE(TRIM(SUBSTRING_INDEX(IPaddress, ',', 1)), ':', ''))) < 7
THEN CONCAT(
TRIM(SUBSTRING_INDEX(IPaddress, ',', 1)),
REPEAT(':0', 8 - (LENGTH(TRIM(SUBSTRING_INDEX(IPaddress, ',', 1))) -
LENGTH(REPLACE(TRIM(SUBSTRING_INDEX(IPaddress, ',', 1)), ':', '')))
))
ELSE TRIM(SUBSTRING_INDEX(IPaddress, ',', 1))
END AS NewIPv6_Patched
FROM daily_player
) a
) b where ip_type='IPv6' and `date` ='2025-01-01'
and NewIPAddress = '2001:268:7394:5b'
) c order by IPNumtest desc;

Additionally, I’ve downloaded an IPv6-to-country mapping file and am using IntervalMatch() in Qlik to associate these numeric IPs to countries.

Thanks & Regards,
Poojashri