Seperating 2 types of data from a field

[shane_spencer]

I've got some data that includes a field called [Client Name]. This field will have either

a) A users PC Name

b) A Server Name

c) An IP Address

From the Server Name or the PC Name I will want to keep only the data to the left of the first '.' but if it's an IP Address I want to keep the full IP. Is there a way to Identify what is an IP Address and what is not? That way I can do an If command to parse what ever data is not an IP. Going forward I may split the data in to 2 fields based on whether it's an IP or not but cannot be sure at this point.

i.e.

127.9.76.112

127.9.75.212

127.9.76.210

ssu1l212-admin.uk.hibm.hsbc

ssu1l212-admin.uk.hibm.hsbc

ssu1l212-admin.uk.hibm.hsbc

ssu1l212-admin.uk.hibm.hsbc

a356g7ldbpntzdu.in.hsbc

a356g7ldbpntzdu.in.hsbc

a356g7ldbpntzdu.in.hsbc

e82617lzwd5d4u2.ho.banking.uk.hsbc

gbl10910.systems.uk.hsbc

a356c7l9h29642n.in.hsbc

ctiexp.systems.uk.hsbc

a35697dzycjn79b.in.hsbc

e82617lzwd5d4u2.ho.banking.uk.hsbc

GBW06547.HBEU.ADROOT.HSBC

11.129.250.17

a35677lzz895ta2.in.hsbc

a356w7lj6a5skgx.in.hsbc

a35697dzycjn79b.in.hsbc

[Anonymous]

if your IP first 3 characters are going to be same like in 127.9.76.112 you can use 127

so you can use left(127.9.76.112,3)='127' then IP )

Thanks

BKC

[Anonymous · Accepted solution]

you may use if (subfield([Client Name],'.',1) to test for ip adress

=if (isnum(subfield(([Client Name],,'.',1),[Client Name], subfield([Cient Name],'.',1) as new Field

if before first "." there are only numbers it is a IP adress and you get complet fied

otherwise you only get text upto first "."

[sasiparupudi1]

if(isnum(subfield(data,'.',1)),trim(data),subfield(data,'.',1))  as data

[jonathandienst]

IP addresses are numeric and will always contain 3 periods:

   if(PurgeChar(Address, '0123456789') = '...', Address, SubField(Address, '.', 1))

[qlikmsg4u]

load *,if(isnum(subfield(Field,'.',1)),Field, subfield(Field,'.',1)) as new_Field;

load * Inline [

Field

127.9.76.112

127.9.75.212

127.9.76.210

ssu1l212-admin.uk.hibm.hsbc

ssu1l212-admin.uk.hibm.hsbc

ssu1l212-admin.uk.hibm.hsbc

ssu1l212-admin.uk.hibm.hsbc

a356g7ldbpntzdu.in.hsbc

a356g7ldbpntzdu.in.hsbc

a356g7ldbpntzdu.in.hsbc

e82617lzwd5d4u2.ho.banking.uk.hsbc

gbl10910.systems.uk.hsbc

a356c7l9h29642n.in.hsbc

ctiexp.systems.uk.hsbc

a35697dzycjn79b.in.hsbc

e82617lzwd5d4u2.ho.banking.uk.hsbc

GBW06547.HBEU.ADROOT.HSBC

11.129.250.17

a35677lzz895ta2.in.hsbc

a356w7lj6a5skgx.in.hsbc

a35697dzycjn79b.in.hsbc

];

[shane_spencer]

Thanx guys! Some very useful suggestions, I've got it working now with your help.