topic Remove extra pipes from concatenated data in QlikView

Remove extra pipes from concatenated data

haymarketpaul — Sat, 11 Jul 2015 12:02:04 GMT

I have a number of concatenated pipe delimited job titles like the following

Job Title1	Job Title2	Job Title3	Job Title4	Job Title5
	Title2	Title3	Title4	Title5
Title1		Title3	Title4
	Title2			Title5

As you can see above some job titles are missing which means the data ends up like this with extra pipes in it...

|Title2|Title3|Title4|Title5

Title||Title3|Title4|

|Title2|||Title5

What i need to do is strip out the extra pipes so the data would end up looking like

Title2|Title3|Title4|Title5

Title1|Title3|Title4

Title2|Title5

Any ideas?

antoniotiman — Sat, 11 Jul 2015 12:08:49 GMT

Hi Paul,

try

=Replace(Replace('|Title2|Title3|Title4|Title5Title||Title3|Title4||Title2|||Title5','||','|'),'||','|')

Regards,

Antonio

swuehl — Sat, 11 Jul 2015 12:08:57 GMT

LOAD

Concat(Pipe,'|') as Pipes

WHERE len(Pipe)

GROUP BY ID;

LOAD Subfield(Pipes,'|') as Pipe, recno() as ID INLINE [

Pipes

|Title2|Title3|Title4|Title5

Title||Title3|Title4|

|Title2|||Title5

];

sunny_talwar — Sat, 11 Jul 2015 12:14:45 GMT

Very nice swuehl‌

haymarketpaul — Sun, 12 Jul 2015 11:40:10 GMT

Awesome - Thank you