Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
psk180590
Contributor III

Ignore Brackets () and load as two different fields

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.

 

Labels (1)
1 Solution

Accepted Solutions
MVP
MVP

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

If the source field is [myfield]:

...
Purgechar(SubField(myfield, ' ', 1), '()') as ID, SubField(myfield, ' ', 2) as Group,
...
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

4 Replies
MVP
MVP

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

If the source field is [myfield]:

...
Purgechar(SubField(myfield, ' ', 1), '()') as ID, SubField(myfield, ' ', 2) as Group,
...
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

tcullinane
Contributor II

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

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
erick_ws
New Contributor II

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

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')
Luminary
Luminary

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

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

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