Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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>'))
Use index function
if (index (field,'...')>0 ,subfield,field)
Hth
sasi
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
no worries glad to help