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?
edit: The field is loaded from an .xlxs-file
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)
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.
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.
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:
Text([IP ADDRESS]) AS [IP ADDRESS]
(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.