Remove Text From A Specific Word Forward

[bxprokop]

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

[sunny_talwar]

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?

[sacosta5]

I'm not sure...

SUBFIELD(DRG_CODE_DESCRIPTION,'W',1)

[maxgro]

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

[bxprokop]

Thanks maxgro.  That worked

[johnw]

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)