topic Re: Parsing numbers from within text in QlikView

Parsing numbers from within text

Margaret — Wed, 16 Dec 2015 20:24:34 GMT

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!

Re: Parsing numbers from within text

sunny_talwar — Wed, 16 Dec 2015 20:34:46 GMT

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)?

Re: Parsing numbers from within text

Margaret — Wed, 16 Dec 2015 20:47:24 GMT

I'll try that.

Yes, the text after the number.

Re: Parsing numbers from within text

sunny_talwar — Wed, 16 Dec 2015 20:53:02 GMT

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

];

Re: Parsing numbers from within text

Margaret — Wed, 16 Dec 2015 22:51:48 GMT

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

Re: Parsing numbers from within text

sunny_talwar — Wed, 16 Dec 2015 23:06:36 GMT

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

Re: Parsing numbers from within text

sunny_talwar — Wed, 16 Dec 2015 23:47:20 GMT

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

Re: Parsing numbers from within text

Margaret — Fri, 18 Dec 2015 00:06:57 GMT

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

Re: Parsing numbers from within text

MarcoWedel — Fri, 18 Dec 2015 00:28:30 GMT

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

Re: Parsing numbers from within text

sunny_talwar — Fri, 18 Dec 2015 11:12:39 GMT

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

Re: Parsing numbers from within text

Margaret — Fri, 18 Dec 2015 15:53:24 GMT

Hah! I got it!

I thought I was doing what you said. But since I did this:

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

I thought I could use "Action Taken" as the new name of the field. But I guess it doesn't get renamed until the whole script loads?

Thank you SOOOO much for your persistence and assistance!!!!!

Re: Parsing numbers from within text

Margaret — Fri, 18 Dec 2015 15:57:34 GMT

I didn't completely try this but thank you for taking the time to try to help me!

Re: Parsing numbers from within text

sunny_talwar — Fri, 18 Dec 2015 16:54:00 GMT

Not a problem

I am glad I was able to help