topic Re: Field containing an IP Adress is misinterpreted in QlikView

Field containing an IP Adress is misinterpreted

oehmemat — Wed, 06 Jun 2018 11:27:51 GMT

Hello Folks,

I'm trying to load a field with IP Addresses, but the Addresses with 3 digits in every Octet is interpreted as a number.

i.e. Address 192.168.100.200 is shown as 192168100200

I already tried using the text() and split() functions with no success.

Any idea how I can load the field as it is?

Regards Matthias

edit: The field is loaded from an .xlxs-file

Re: Field containing an IP Adress is misinterpreted

mayankraoka — Wed, 06 Jun 2018 11:37:29 GMT

what is your data source?if it is excel try to change IP address column from general to text and try.

Regards,

Mayank

Re: Field containing an IP Adress is misinterpreted

oehmemat — Wed, 06 Jun 2018 11:40:37 GMT

It's a .xlsx-file

Re: Field containing an IP Adress is misinterpreted

vvvvvvizard — Wed, 06 Jun 2018 11:52:20 GMT

Try Replace(IP,'.',':') as IP

so the , or . wont be mistaken as decimal or seperator

Re: Field containing an IP Adress is misinterpreted

sergio0592 — Wed, 06 Jun 2018 11:55:34 GMT

Hi, and if you try with :

load mid(IP Addresses,1,3)&'.'&mid(IP Addresses,4,3)&'.'&mid(IP Addresses,7,3)&'.'&mid(IP Addresses,10,3)

Re: Field containing an IP Adress is misinterpreted

marcus_sommer — Wed, 06 Jun 2018 12:30:29 GMT

Quite probably is your IP in Excel not an IP else just a formated number and you could also apply the same formatting in Qlik with something like: num(IP, '#.##0') or maybe as string with text(num(IP, '#.##0')) if you need it as key-field or to build links.

- Marcus

Re: Field containing an IP Adress is misinterpreted

oehmemat — Wed, 06 Jun 2018 12:36:01 GMT

Thank you for your efforts, but I had no luck with the suggestions.

It seems Qlikview is omitting the periods before applying any functions, so the mid(), replace() and split() functions aren't working. It happens only for IP-Addresses with 3 digits in all octets, Addresses with 2 digits in one of the octets are working fine.

Changing the ThousandSep-Variable doesn't help too.

Re: Field containing an IP Adress is misinterpreted

oehmemat — Wed, 06 Jun 2018 12:50:19 GMT

I now use the following:

if (FindOneOf(IPAddress,'.') >0,text(IPAddress),text(num(IPAddress, '#.##0'))) as IPAddress;

Re: Field containing an IP Adress is misinterpreted

petter — Wed, 06 Jun 2018 18:43:03 GMT

I am able to reproduce your problem if I set the thousand separator to a period. However if I wrap the fieldname in a Text() function it will not interpret them as a number with thousand separators and will read it in as text for me. This is my load script:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

LOAD

Text([IP ADDRESS]) AS [IP ADDRESS]

FROM

[IP-addresses.xlsx]

(ooxml, embedded labels, table is Sheet1);

I am quite certain that it is the IP addresses in Excel that is the problem. Excel interprets them as numbers and displays them with periods so you might not have noticed that. You can check whether the IP-addresses with 3 digits in each octet is right aligned automatically (you will have to remove formatting first if they have been manually left or right aligned) or you could simply use the ISNUMBER(<cell-ref>) function in the column to the right of the IP-adresses and see whether it returns true or false for each of the IPs.