
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Replace(Field1, '|', ' ')


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
in your case
purgechar(fieldname,'|') as fieldname
this should give the desired output

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
Please close the thread by marking correct answer & give likes if you like the post.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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);
Dropped the Fields.
