topic splitting a string during load in QlikView

splitting a string during load

Wed, 04 Jul 2012 07:58:06 GMT

Hi all,

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?

Many thanks

Mark

Re: splitting a string during load

Anonymous — Wed, 04 Jul 2012 08:42:45 GMT

Hi Mark,

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

Re: splitting a string during load

Wed, 04 Jul 2012 11:36:48 GMT

Hi Johannes,

Thanks,

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.

Thanks for your help

Mark