Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
oehmemat
New 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
Valued Contributor

Re: Field containing an IP Adress is misinterpreted

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

Regards,

Mayank

oehmemat
New Contributor III

Re: Field containing an IP Adress is misinterpreted

It's a .xlsx-file

vvvvvvizard
Contributor III

Re: Field containing an IP Adress is misinterpreted

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

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

sergio0592
Valued Contributor II

Re: Field containing an IP Adress is misinterpreted

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)

MVP & Luminary
MVP & Luminary

Re: Field containing an IP Adress is misinterpreted

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
New Contributor III

Re: Field containing an IP Adress is misinterpreted

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
New Contributor III

Re: Field containing an IP Adress is misinterpreted

I now use the following:

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

MVP
MVP

Re: Field containing an IP Adress is misinterpreted

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.