topic Removing numbers from a Text String in QlikView

Removing numbers from a Text String

Anonymous — Fri, 04 Mar 2016 16:28:09 GMT

Hi Guys I need your help removing 2 numerical values as 2 diff fields from one Text String. Pls see below:

Field Text

month is november 70 % work done accounting age group < 3 Finance age Group < 5 fiscal year 2021

month is november 70 % work done accounting age group < 3 Finance age Group > 5 fiscal year 2021

month is november 70 % work done accounting age group = 3 Finance age Group = 5 fiscal year 2021

70 % work done accounting age group < 1100 fiscal year 2021 monthend invoice accounts payable Finance age Group > 500

71 % work done accounting age group < 1600 fiscal year 2021 monthend invoice accounts payable Finance age Group > 20

each row has number located after 'Finance age Group <' or 'accounting age group <' with the exception '<' or '>' can be '='

so the jist of what i want is;

the numbers I need removed are:

1) accounting age group < 3 : I need '3' as a number 3 parsed out in a caluclated field Accounting Age

2) Finance age Group < 5 : I need '5' as a number 5 parsed out in a caluclated field Finance Age

Thanks so much I really appreciate

Re: Removing numbers from a Text String

swuehl — Fri, 04 Mar 2016 16:34:32 GMT

Maybe something like

Textbetween( Replace(TEXTFIELD, '=','<'), 'Finance age Group < ',' ') as FinanceAge

Re: Removing numbers from a Text String

sunny_talwar — Fri, 04 Mar 2016 16:37:44 GMT

May be too customized, but seem to work for the provided dataset:

Table:

LOAD Text,

KeepChar(Left(TextBetween(Text, 'accounting age group', 'Finance age Group'), 8), '1234567890') as [Accounting Age],

KeepChar(Left(SubField(Text, 'Finance age Group', 2), 8), '1234567890') as [Finance Age];

LOAD * Inline [

Text

month is november 70 % work done accounting age group < 3 Finance age Group < 5 fiscal year 2021

month is november 70 % work done accounting age group < 3 Finance age Group > 5 fiscal year 2021

month is november 70 % work done accounting age group = 3 Finance age Group = 5 fiscal year 2021

70 % work done accounting age group < 1100 fiscal year 2021 monthend invoice accounts payable Finance age Group > 500

71 % work done accounting age group < 1600 fiscal year 2021 monthend invoice accounts payable Finance age Group > 20

];

Re: Removing numbers from a Text String

sebastianlettner — Fri, 04 Mar 2016 16:53:41 GMT

Hi,

this should work for you.

Source:
LOAD * INLINE
[
    FieldText
    month is november 70 % work done accounting age group < 3  Finance age Group < 5 fiscal year 2021
    month is november 70 % work done accounting age group < 3  Finance age Group > 5 fiscal year 2021
    month is november 70 % work done accounting age group = 3  Finance age Group = 5 fiscal year 2021
    70 % work done accounting age group < 1100 fiscal year 2021 monthend invoice accounts payable Finance age Group > 500
    71 % work done accounting age group < 1600 fiscal year 2021 monthend invoice accounts payable Finance age Group > 20
];


Out:
LOAD
    FieldText,
    left(aagRP, 1) as aagSign,                                                    //Sign after accounting age group
    left(fagRP, 1) as fagSign,                                                    //Sign after Finance age Group
    Mid(aagRP, 2, if(aagNE = 0, Len(aagRP) + 1, aagNE) - 2) as aagNum,            //Number after accounting age group
    Mid(fagRP, 2, if(fagNE = 0, Len(fagRP) + 1, fagNE) - 2) as fagNum;            //Number after Finance age Group
LOAD
    *,
    index(aagRP, ' ', 2) as aagNE,
    index(fagRP, ' ', 2) as fagNE;
LOAD
    *,
    right(FieldText, Len(FieldText) - aagStart - 20) as aagRP,    //Right part after accounting age group
    right(FieldText, Len(FieldText) - fagStart - 17) as fagRP;    //Right Part after Finance age Group
LOAD
    FieldText,
    index(FieldText, 'accounting age group') as aagStart,        //Start index of accounting age group
    index(FieldText, 'Finance age Group') as fagStart            //Start index of Finance age Group
Resident Source;

Regards

Sebastian Lettner

Re: Removing numbers from a Text String

Peter_Cammaert — Fri, 04 Mar 2016 17:13:41 GMT

It seems I don't need to stay up at night anymore: parse a numerical value from a string text

Care to close that discussion if you start another one?

Re: Removing numbers from a Text String

Anonymous — Sat, 05 Mar 2016 03:50:36 GMT

Thanks everyone. I will try Sebastian's soln and choose the correct answer.

Peter, when there is an urgency, the desire to deliver overrides the will to remain righteous

I agree with your point though, and the humor was spot on