Solved: Re: Seperating 2 types of data from a field - Qlik Community

shane_spencer · ‎2015-08-07

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

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 · ‎2015-08-07

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 "."

View solution in original post

Anonymous · ‎2015-08-07

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 · ‎2015-08-07

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 · ‎2015-08-07

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

jonathandienst · ‎2015-08-07

IP addresses are numeric and will always contain 3 periods:

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

qlikmsg4u · ‎2015-08-07

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

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 · ‎2015-08-07

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