Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Is it possible to Use Subfield for more than one criteria

Hi

I have a text table that is in one Cell on a spreadsheet

Example below

In our database we have a table that I want to split this data out into new fields.

I have used the following expression in the load script    Trim(SubField(mnoteshistory, '...'))  as Last_Contact

The problem is that not all entries in the data is separated by a '...' how would I separate by '...' and 'none'

Thanks for any assistance

30/03/2015 10:35:28 - Bob Jones
...

27/03/2015 16:52:34 - credit control
...

27/03/2015 16:07:30 - credit control
...

12/03/2015 15:43:47 - credit control
...

12/03/2015 15:42:15 - credit control
...

12/03/2015 15:35:25 - credit control
...

12/03/2015 14:23:40 - credit control
...

27/01/2015 15:48:14 - Sue Smith
none

12/01/2015 14:03:15 - Jill Dixon
none

Regards

Dave

1 Solution

Accepted Solutions
Not applicable
Author

Thanks Joe for your help.

Both work well, I think I will use the substring map as not fully explored the data yet and will give me the flexibility I need.

CHEERS

View solution in original post

5 Replies
Not applicable
Author

Hi Dave,

You can maybe do a replace on the none text and just use the single subfield then

Trim(SubField(Replace(mnoteshistory,'none','...'), '...'))


hope that helps

Joe

Not applicable
Author

another option that you have is to use a substring map, which might be better if you have more than just those two potential values for you subfield.

Map:

Mapping Load * Inline [

old,new

none,<Sub>

...,<Sub>

aaa,<Sub>

bbb,<Sub>

];

Trim(SubField(MapSubString('Map',mnoteshistory), '<Sub>'))

sasiparupudi1
Master III
Master III

Use index function

if (index (field,'...')>0 ,subfield,field)

Hth

sasi

Not applicable
Author

Thanks Joe for your help.

Both work well, I think I will use the substring map as not fully explored the data yet and will give me the flexibility I need.

CHEERS

Not applicable
Author

no worries glad to help