topic Re: Ignore Brackets () and load as two different fields in QlikView

Ignore Brackets () and load as two different fields

psk180590 — Sat, 16 Nov 2024 21:32:37 GMT

Hello All,

I have the following data in my database as a single field.

(84700) Pharmacy.

From this field i would like to extract Two fileds.

84700 as ID and Pharmacy as Group.

Please, note i would like to ignore the brackets () here.

Can someone please provide some pointers.

TIA

Sai.

Re: Ignore Brackets () and load as two different fields

jonathandienst — Thu, 24 Jan 2019 12:23:53 GMT

If the source field is [myfield]:

...
Purgechar(SubField(myfield, ' ', 1), '()') as ID,
SubField(myfield, ' ', 2) as Group,
...

Re: Ignore Brackets () and load as two different fields

tcullinane — Thu, 24 Jan 2019 12:24:09 GMT

assuming you dont have brackets you want to keep in any place in the field
use subfield() to split and replace() to remove the extra leading bracket so;
replace(subfield(FieldName,')',1),'(','') as ID,
trim(subfield(FieldName,')',2)) as Group

Re: Ignore Brackets () and load as two different fields

erick_ws — Thu, 24 Jan 2019 12:24:25 GMT

Hi,
You can use:
TextBetween(SINGLE_FIELD, '(' , ')') AS ID, //Getting the text between '(' and ')'
MID(SINGLE_FIELD, index(SINGLE_FIELD, ' ')+1) AS Group //Getting the substring from the original field, assuming that there will be always an SPACE between the ID and the Group. (the "+1" is to get the string 1 position after the SPACE, or else it was going to bring ' Pharmacy')

Re: Ignore Brackets () and load as two different fields

Ivan_Bozov — Thu, 24 Jan 2019 12:31:48 GMT

LOAD
PURGECHAR(PURGECHAR(SUBFIELD(Field, ' ', 1),'('),')') AS ID,
SUBFIELD(Field, ' ', 2) AS Group
FROM...

Oh, well, so many people were faster than me. 😄