In the db/app I am building we have paid for an external mapping of string terms to a variety of "categories". The results have been returned to me in a table like this:
Key Field
Categories
a string
[cat1,cat2]
another string
[cat2,cat3,cat4]
yet another string
[cat2, cat4]
other string
[cat1, cat5]
further string
[cat2, cat4]
The categories field rows are strings surrounded by square brackets and delimeted by ",".
I want to load the data so that I end up with a table like this:
Key Field
Categories
a string
cat1
a string
cat2
another string
cat2
another string
cat3
another string
cat4
yet another string
cat2
yet another string
cat4
other string
cat1
other string
cat5
further string
cat2
further string
cat4
So that I am able to look at all the Key field strings associated with a certain category.
e.g in this case cat1 would be associated with "a string" and "other string"
Can you give me some pointers on how to achieve this?
You can use the SubField() function in the script.
Subfield(Categories,',') as Categories
You might want to strip out the [ and ] as well if they are there in the data.. use Subfield(Purgechar(Categories,'[]'),',') as Categories in that case
you know I had already tried the substring() function, and had not got it to work, but follwoing your reply, I employed it then realised that I had made an error in my script and not got the key field correct!
I correct that error and the data loaded great first time.
