Parsing numbers from within text

[Margaret]

In a table named FollowUps, in a Column named FollowUpAction, I have values that usually look like this

"Other action taken 9322 Counseled Appropriately"

When they look like that (Other action taken #### [further text], where [further text] is a few words that vary), I need to parse out the 4 digit number (always 4 digits) and the text after the 4 digit number into two different columns.

1) How do I do that?

2) How would I figure similar function/coding questions out if I didn't have this forum or needed an answer asap?

Thanks so much!

[sunny_talwar]

Parsing out the number should be fairly simple assuming rest of the portion won't have any numbers (like in your example)

KeepChar(FollowUpAction, '0123456789') as Number

For the second part, do you just need the text after the number (Counseled Appropritely)?

[Margaret]

I'll try that.

Yes, the text after the number.

[sunny_talwar · Accepted solution]

See if this works:

Table:

LOAD *,

  KeepChar(Original, '0123456789') as Number,

  Trim(SubField(Original, KeepChar(Original, '0123456789'), 2)) as Text_After_Number;

LOAD * Inline [

Original

Other action taken 9322 Counseled Appropriately

Blah Blah 2024 Something Something

Blah Blah 40394 Nothing Something

Blah 2320 Nothing

Blakch abldgfs dbshwnd 43049 Anything Nothing

];

[Margaret]

Is this SQL or Qlikview's language?

Because Qlikview doesn't seem to recognize "KeepChar". And this table is from an Excel spreadsheet.

Is that why it's not working?

Als

[sunny_talwar]

QlikView does have a KeepChar() function. Attached sample shows how this is working

[sunny_talwar]

Check it out here: QlikView ‒ KeepChar - script and chart function

[Margaret]

Thank you.

I tried it and just got empty columns with the names I assigned:

LOAD [Event ID],

     [Followup Immediate Action(s) Taken] as ActionTaken,

     KeepChar('ActionTaken', '0123456789') as ProviderID,

     Trim(SubField('ActionTaken', KeepChar('ActionTaken', '0123456789'), 2)) as Trend

FROM

[MarcoWedel]

Hi,

one solution might be also:

table1:
LOAD FollowUpAction,
    Left(SubFollUpAct,4) as NumberField,
    Mid(SubFollUpAct,6) as [further text];
LOAD *,
    SubField(FollowUpAction,'Other action taken ',2) as SubFollUpAct
Inline [
FollowUpAction
Some action taken 9322 Counseled Appropriately
Other action taken 9323 Counseled Appropriately
Any action taken 9324 Counseled Appropriately
Other action taken 9325 some Text after number
];

hope this helps

regards

Marco

[sunny_talwar]

You need the field name and not static text here:

LOAD [Event ID],

    [Followup Immediate Action(s) Taken] as ActionTaken,

    KeepChar([Followup Immediate Action(s) Taken], '0123456789') as ProviderID,

    Trim(SubField([Followup Immediate Action(s) Taken], KeepChar([Followup Immediate Action(s) Taken], '0123456789'), 2)) as Trend

FROM

Assuming this is [Followup Immediate Action(s) Taken] is the field where we are trying to do this parsing