Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
shane_spencer
Specialist
Specialist

Seperating 2 types of data from a field

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

1 Solution

Accepted Solutions
Anonymous
Not applicable

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

6 Replies
Anonymous
Not applicable

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
Not applicable

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
Master III
Master III

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

jonathandienst
Partner - Champion III
Partner - Champion III

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
Specialist
Specialist

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
Specialist
Specialist
Author

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