QlikView text manipulation question

[m_monfort]

Hi all,

I've been looking at the various text manipulation options in script but cannot seem to find a way forward so need the communities help.

I have a list of data that comes through with headers as follows:

AA10P

AA10C

AA10SPF

AA11.1P

AA11.1C

AA11.1SPF

AA23.2/45P

AA23.2/45C

AA23.2/45SPF

The unique fields above cover only 3 industry codes, AA10, AA11.1 and AA23.2

If these were of a fixed length I could just use a left( ) function but the size varies.

Additionally, I also want to pull out the measure portion which in this example is the P, C and SPF values shown at the end of each string.

How can I do this in the script?

If it helps, there are always 2 letters to start off for each industry (ie there will always be 2 letters like AA followed by a set of values that are just numbers (eg AA10), have decimal points (eg AA11.1) or have a another decimal point and/or separator (eg AA23.2/45)

Please help

[m_monfort]

Hi Vish - you're right - it can have any 2 letter code at the start as the region code

I did try your solution but didn't work for some reason - I'll try again

[MarcoWedel]

So you simply want to purge/extract the last characters from a text that all are letters?

[m_monfort]

Hi Marco - essentially that would be it. Wherever the number ends I need the latter half to be the measure and the first part to be an identifier code.

If it was AU15.11/2XXXX the measure is the XXXX and the AU15.11/2 would be the identifer.

The measure (XXXX) can be any combination of only letters but it can be any length - it is not fixed.

is there a way to handle that?

[MarcoWedel · Accepted solution]

sounds like this could be a solution:

table1:
LOAD *,
    Left(Header,FindOneOf(Header,PurgeChar(Header,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),-1)) as IndustryCode,
    Mid(Header,FindOneOf(Header,PurgeChar(Header,'ABCDEFGHIJKLMNOPQRSTUVWXYZ'),-1)+1) as Measure
Inline [
    Header
    AA10P
    AA10C
    AA10SPF
    AA11.1P
    AA11.1C
    AA11.1SPF
    AA23.2/45P
    AA23.2/45C
    AA23.2/45SPF
    AB1A
    AB1\2B
    AB1.1C
    AB1.1|23D
    AB12E
    AB12.3FG
    AB123.45H
    AB123.456/789IJ
    BB1
    BB23
    BB34.5
    BB456.7A
    BB5678.90/0.12BC         
];

hope this helps

regards

Marco

[m_monfort]

Perfect! Thanks Marco