Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
psk180590
Creator III
Creator 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
jonathandienst
Partner
Partner

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
jonathandienst
Partner
Partner

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
Creator II
Creator II

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

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

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

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

vizmind.eu