Purgechar challenge

[marcel_olmo]

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.

[#2]

Try with substring function

[MarcoWedel]

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

[marcel_olmo]

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) ;

[MarcoWedel · Accepted solution]

Hi,

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

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

could solve the issue.

Regards

Marco

[marcel_olmo]

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

[MarcoWedel]

You're welcome

Regards

Marco