Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
qlikdash
Specialist
Specialist

Removing numbers from a Text String

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

1 Solution

Accepted Solutions
sunny_talwar

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

];


Capture.PNG

View solution in original post

5 Replies
swuehl
MVP
MVP

Maybe something like

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

sunny_talwar

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

];


Capture.PNG

View solution in original post

sebastianlettner

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

Peter_Cammaert
Partner
Partner

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?

qlikdash
Specialist
Specialist
Author

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