topic Re: Is it possible to Use Subfield for more than one criteria in QlikView

Is it possible to Use Subfield for more than one criteria

Fri, 31 Jul 2015 14:22:10 GMT

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

Re: Is it possible to Use Subfield for more than one criteria

Fri, 31 Jul 2015 14:29:17 GMT

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

Re: Is it possible to Use Subfield for more than one criteria

Fri, 31 Jul 2015 14:43:05 GMT

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,

...,

aaa,

bbb,

];

Trim(SubField(MapSubString('Map',mnoteshistory), ''))

Re: Is it possible to Use Subfield for more than one criteria

sasiparupudi1 — Fri, 31 Jul 2015 14:49:57 GMT

Use index function

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

Hth

sasi

Re: Is it possible to Use Subfield for more than one criteria

Fri, 31 Jul 2015 14:51:25 GMT

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

Re: Is it possible to Use Subfield for more than one criteria

Fri, 31 Jul 2015 15:47:13 GMT

no worries glad to help