topic Trim string from between specific characters in QlikView

Trim string from between specific characters

dawgfather — Thu, 03 Dec 2015 02:10:18 GMT

I have a set of data - essentially computer names and folders - and I need to remove the domain part of the string and leave the very beginning and the trailing part (after the .com perhaps). For example:

Source data is

server1.domain1.com:/vol/folder1

server1.domain1.com:/vol/folder2

server2.domain2.net:/vol/folder3

server3.domain.us:/folder4

What I want is to remove from the first "." up to, but not including, the ":" to get

server1:/vol/folder1

server1:/vol/folder2

server2:/vol/folder3

server3:/folder4

Any ideas?

Re: Trim string from between specific characters

sunny_talwar — Thu, 03 Dec 2015 12:04:21 GMT

May be this:

Table:

LOAD SubField(Col, '.', 1) & ':' & SubField(Col, ':', 2) as NewCol,

Col;

LOAD * Inline [

Col

server1.domain1.com:/vol/folder1

server1.domain1.com:/vol/folder2

server2.domain2.net:/vol/folder3

server3.domain.us:/folder4

];

Re: Trim string from between specific characters

teiswamsler — Thu, 03 Dec 2015 12:05:41 GMT

Hi Scott

This will Work for you

SubField( ServerName, '.', 1) & mid( ServerName, index( ServerName, ':' ) )

/Teis

Re: Trim string from between specific characters

tresB — Thu, 03 Dec 2015 12:07:30 GMT

SubField(Data, '.',1) & ':' & Subfield(Data, ':',2)

Re: Trim string from between specific characters

t_chetirbok — Thu, 03 Dec 2015 12:11:16 GMT

Hi!

SubField(str,'.dom',1) & right(str, len(str)-index(str,':/')+1)

Re: Trim string from between specific characters

Thu, 03 Dec 2015 12:25:20 GMT

Hi,

Try this.Hoping the data format could be the same throughout.

load SubField(string,'.',1)&':'&SubField(string,':',2) ;

load * inline

[

string

server1.domain1.com:/vol/folder1

server1.domain1.com:/vol/folder2

server2.domain2.net:/vol/folder3

server3.domain.us:/folder4

];