Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator III

Removing Trailing Characters

I data in a field which is typically in the following pipe delimited format

Text1|||||

Text1|Text2|Text3|||

Text1|Text2||||

I would like to remove all the trailing pipes - however many there are

I was trying something a long the lines of this but it doesn't quite seem to work...

Mid(Field1,1,FindOneOf(Field1,'|',-1)-1)   AS    Field1

Any ideas greatly appreciated

Tags (2)
7 Replies
Highlighted

Re: Removing Trailing Characters

Use purgechar function

PurgeChar(s1, s2)

Returns the string s1 less all characters contained in string s2.

Example:

purgechar ( 'a1b2c3','123' ) returns 'abc'

Highlighted

Re: Removing Trailing Characters

Replace(Field1, '|', ' ')

Highlighted

Re: Removing Trailing Characters

in your case

purgechar(fieldname,'|') as fieldname

this should give the desired  output

Highlighted
Creator III
Creator III

Re: Removing Trailing Characters

I only want to get rid of the trailing pipes at the end - i think those 2 solutions would also affect the genuine pipes in the middle as well

Highlighted

Re: Removing Trailing Characters

Hi

Try like this

"I would like to remove all the trailing pipes "

LOAD Concat(T,'|') as T1 where Len(Trim(T));

LOAD (SubField('Text1|Text2|Text3|||','|'))  as T AutoGenerate 1;

Highlighted
Contributor
Contributor

Re: Removing Trailing Characters

Don't know if you still have this problem, but the solution I used in Qlik Sense was this:

 

replace(trim(replace(Keepchar(concat(distinct SalesOrder&' | '), '1234567890|'), '|', ' ')), ' ', '|')

 

So, I first replaced the '|' with the space-character. Then I used Trim() to  remove the trailing spaces, and then replace to convert the spaces back to '|'. This of course only works if you have no spaces in the data to process.

If you have spaces, you might have to replace first to get them out of the way, and revert them back once your done. 

Highlighted
Specialist III
Specialist III

Re: Removing Trailing Characters

One solution in the Script. Drop the Fields which doesn't have data.

tab1:
LOAD * INLINE [
    Text1|||||
    Text1|Text2|Text3|||
    Text1|Text2||||
](delimiter is '|', no labels);

Set vDropFields='';
FOR i = 1 to NoOfFields('tab1')  
  LET vFieldName = FieldName($(i),'tab1');  
  LET vFieldValueCount = FieldValueCount('$(vFieldName)');    

  If $(vFieldValueCount)=1 And FieldValue('$(vFieldName)', 1)='' Then
  	Trace ***Inside;
  	Let vDropFields = '$(vDropFields)'&If('$(vDropFields)'<>'',',')&'$(vFieldName)';
  EndIf 
NEXT i  

Drop Fields $(vDropFields);

commQV05.PNG

Dropped the Fields.

commQV06.PNG