topic Re: Purgechar challenge in QlikView

Purgechar challenge

marcel_olmo — Sat, 24 Jan 2015 16:16:15 GMT

Hi guys, I have a row of a concatenated values like this : NNN-fieldNNfield. And I'd like to convert it to fieldNNfield. The problem is that if I use the funciont purgechar() to substract the numbers, I'll erase the numbers inside the field too.

Do you have any ideas about how to do it?

Here's a dummy example about what I'm talking about :

FieldOrigin

801-field1name,905-field2name,202-field3name

And I want to convert it into :

FieldDesired

field1name,field2name,field3name

*(The terms field or name are invented for this example, the field could be named with other names, but with numbers inside)

Regards, Marcel.

Re: Purgechar challenge

Sat, 24 Jan 2015 16:20:24 GMT

Try with substring function

Re: Purgechar challenge

MarcoWedel — Sat, 24 Jan 2015 16:42:52 GMT

Hi,

you could try with

Load somefields,

TextBetween(FieldOrigin&',', '-', ',', IterNo()) as FieldDesired

from ...

While IterNo()<=SubStringCount(FieldOrigin, '-');

This should create one row per subfield.

You could concatenate the result afterwards.

Regards

Marco

Re: Purgechar challenge

marcel_olmo — Sat, 24 Jan 2015 16:56:32 GMT

Thanks Marco for your help.

The original point of that question is that I have an original table like this :

ID	Field
1	field5
2	field3
3	field1

And I'd like to convert it in :

Field

field5,field3,field1

But when I'm trying to load the original table and make the resident table with the concat function, it always put me the fields in order of the field, not the id. It always does this :

Field

field1,field3,field5

Have you experienced something like this before?

I'm trying to do it like this with no success :

DesiredTable :

NoConcatenate

load Distinct

concat(Field,',') as [New Field]

Resident Original table Order by lD asc (or desc) ;

Re: Purgechar challenge

MarcoWedel — Sat, 24 Jan 2015 17:07:04 GMT

Hi,

concat can have a third parameter for order sequence, so:

concat(Field,',', ID) as [New Field]

could solve the issue.

Regards

Marco

Re: Purgechar challenge

marcel_olmo — Sat, 24 Jan 2015 17:12:32 GMT

You're right Marco, I forgot that! Regards, Marcel.

Re: Purgechar challenge

MarcoWedel — Sat, 24 Jan 2015 20:12:14 GMT

You're welcome

Regards

Marco