Field containing an IP Adress is misinterpreted

[oehmemat]

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

[mayankraoka]

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

Regards,

Mayank

[oehmemat]

It's a .xlsx-file

[vvvvvvizard]

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

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

[sergio0592]

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]

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]

I now use the following:

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

[petter]

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.