topic Remove Text From A Specific Word Forward in QlikView

Remove Text From A Specific Word Forward

bxprokop — Wed, 28 Dec 2016 16:04:52 GMT

Hi Qlik Community,

I am looking to parce out only the text before a specific word or combination of words in the script. In particular, I am looking to parce out and create a new field everything before either W/O CC/MCC or W CC or W MCC in the subset of data below. When done this would create a new field with CRANIOTOMY & ENDOVASCULAR INTRACRANIAL PROCEDURES (for DRG 25-27) and SPINAL PROCEDURES (for DRG 28-30). Any help would be greatly appreciated.

DRG_CODE	DRG_CODE_DESCRIPTION
025	CRANIOTOMY & ENDOVASCULAR INTRACRANIAL PROCEDURES W MCC
026	CRANIOTOMY & ENDOVASCULAR INTRACRANIAL PROCEDURES W CC
027	CRANIOTOMY & ENDOVASCULAR INTRACRANIAL PROCEDURES W/O CC/MCC
028	SPINAL PROCEDURES W MCC
029	SPINAL PROCEDURES W CC OR SPINAL NEUROSTIMULATORS
030	SPINAL PROCEDURES W/O CC/MCC

Thanks

Brian

Re: Remove Text From A Specific Word Forward

sunny_talwar — Wed, 28 Dec 2016 16:43:03 GMT

I gues that you are looking to get rid of the last portion, but what is the requirement for the new column you need? I am not sure I understand that part of the thread? Would you be able to put down the required output for the above data you have provided?

Re: Remove Text From A Specific Word Forward

sacosta5 — Wed, 28 Dec 2016 17:16:18 GMT

I'm not sure...

SUBFIELD(DRG_CODE_DESCRIPTION,'W',1)

Re: Remove Text From A Specific Word Forward

maxgro — Wed, 28 Dec 2016 18:29:47 GMT

in script or in chart you can use the bold

LOAD

DRG_CODE,

DRG_CODE_DESCRIPTION,

left(DRG_CODE_DESCRIPTION,

Pick(WildMatch(DRG_CODE_DESCRIPTION, '*W/O CC/MCC*', '*W CC*', '*W MCC*'),

index(DRG_CODE_DESCRIPTION, 'W/O CC/MCC'),

index(DRG_CODE_DESCRIPTION, 'W CC'),

index(DRG_CODE_DESCRIPTION, 'W MCC')

)

-1

) as NewField

FROM

[https://community.qlik.com/thread/244619] (html, codepage is 1252, embedded labels, table is @1);

Re: Remove Text From A Specific Word Forward

bxprokop — Wed, 28 Dec 2016 22:06:43 GMT

Thanks maxgro. That worked

Re: Remove Text From A Specific Word Forward

johnw — Wed, 28 Dec 2016 22:48:09 GMT

Or in case we have a W in any of our descriptions, maybe:

subfield(subfield(subfield(DRG_CODE_DESCRIPTION,' W/O CC/MCC',1),' W CC',1),' W MCC',1)