Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
oehmemat
Contributor III
Contributor III

Field containing an IP Adress is misinterpreted

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

8 Replies
mayankraoka
Specialist
Specialist

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

Regards,

Mayank

oehmemat
Contributor III
Contributor III
Author

It's a .xlsx-file

vvvvvvizard
Partner - Specialist
Partner - Specialist

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

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

sergio0592
Specialist III
Specialist III

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)

marcus_sommer

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

oehmemat
Contributor III
Contributor III
Author

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.

oehmemat
Contributor III
Contributor III
Author

I now use the following:

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

petter
Partner - Champion III
Partner - Champion III

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.