Find 'x'-n in field and create n new fields

[#1]

Hi

I have a field with a lot of text inside and in between e.g. some dates. With the TextBetween() I can find them and make a new field for each, so I have them isolated. So one field for each finding.

The problem is that I dont know how many of these dates I have, sometimes its 2, sometimes it is 10 or more.  Perhaps I could use the substringcount() to find the max instances and then use that to create the number of new fields with the TextBetween() as field1 ... as field2 and so on?

[Anonymous]

normally regex is the way to handle this but QlikView does not support it unfortunately. there is a workaround though to use VBS explained by Barry Harmsen

http://www.qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/

So you will need to parse your strings using regex that can identify multiple matches (dates in your case) and then create rows of data in your script manually.

If you can use ETL tool, I would suggest to look at Pentaho which is awesome free open source tool - you can do these manipulations there and it will be a great tool to have in your toolbox.

[Gysbert_Wassenaar]

Have a look at the subfield function. If this doesn't help enough post some example source data.

[#4]

Thx you both, interesting with the regex in Qlikview, and yes the subfield could be an option, but in this case I think TextBetween() works fine for finding/searching/localise the strings. The problem is that I dont know how many of these to make, so I need some kind of loop/autofield function to generate the field.

So I have

...

TextBetween(..., 1) as Field1

TextBetween(..., 2) as Field2

TextBetween(..., 3) as Field3

TextBetween(..., n) as Fieldn

In other Words I can find them and create them if I know how many there is, but this changing with the data loaded. So that is why I want to count with substringcount the max row, and then in some way create the number of fields.

I might find a data example later if still needed.        

[Anonymous]

well if you have unique ID on your row, you can just do a loop and append additional elements to that ID as you go.

If you attach an example of your QVD with data, we can figure it out.

[#6]

Why not use

PurgeChar(Field,'1234567890') as TextField

or please attach a sample if this is not what you are looking for.

Thanks

[#7]

Thx but I dont have an ID.

I must admit anyway that I can use the Subfield as Gysbert suggested, I just had to use it properly and then I can actually avoid the problem I created, so no fancy loops or autofield this time.