topic split a field into different fields in QlikView

split a field into different fields

Tue, 12 Aug 2014 14:37:23 GMT

I have a field named 'sources'. For examples 1A_florida, 1B_florida, 1C_florida, 1A_georgia, 1B_georgia, 1C_georgia

If I want to create a new field '1A', '1B', '1C'. What is the best way to do this?

Re: split a field into different fields

its_anandrjs — Tue, 12 Aug 2014 14:47:41 GMT

Best way is use Subfield function see the example for that

LOAD sources,SubField(sources,'_',1) as Newsources;

LOAD * Inline

[

sources

1A_florida

1B_florida

1C_florida

1A_georgia

1B_georgia

1C_georgia

];

Re: split a field into different fields

Tue, 12 Aug 2014 15:35:23 GMT

what does the 1 mean in (sources, '_', 1) ?

Re: split a field into different fields

Miguel_Angel_Baeyens — Tue, 12 Aug 2014 15:37:35 GMT

Subfield() extracts

from the string specified in the first parameter,
using the separator specified in the second parameter, all values (if there is no third parameter)
or the position specified in third parameter regarding the separator.

In your case, the first position. If you specify 2 instead you will get "florida" and "georgia".

Miguel

Re: split a field into different fields

Tue, 12 Aug 2014 15:48:56 GMT

I have some fields that also that are 1Bflorida and 1Bgeorgia without the _ delimiter.

If I wanted to create a new field with all '1B' values, what third parameter would I use to show me both 1Bflorida and 1B_florida

Re: split a field into different fields

Miguel_Angel_Baeyens — Tue, 12 Aug 2014 15:51:24 GMT

If that is the case, then you need to use the Left() function instead, and keep only the first two characters:

Left('1Bflorida', 2) // returns 1B

Left('1B_florida', 2) // also returns 1B

Miguel

Re: split a field into different fields

Tue, 12 Aug 2014 15:57:46 GMT

That won't work because then I will have to go through each single 'source' (there are over 100 sources) and do the left() function

Re: split a field into different fields

Tue, 12 Aug 2014 16:03:38 GMT

Miguel's solution works:

LOAD sources,left(sources,2) as Newsources;
LOAD * Inline
[
sources
1A_florida
1B_florida
1C_florida
1A_georgia
1B_georgia
1C_georgia
];

Re: split a field into different fields

its_anandrjs — Tue, 12 Aug 2014 16:13:21 GMT

In the expression (sources, '_', 1) 1 is parameter means from the first position in the source field extract the string. As Miguel explain if your requirement is that in the string there is no symbol _ then use left function and if you know you have to pick only first two word then use left function.

Re: split a field into different fields

MarcoWedel — Tue, 12 Aug 2014 16:17:53 GMT

Does this mean that the source could be longer than 2 characters?

Can you post some more examples to clarify the range of possible values?

Thanks

Regards

Marco

Re: split a field into different fields

Tue, 12 Aug 2014 16:35:54 GMT

I was thinking of displaying it in a different way but Miquel's solution is actually a better idea. Thanks everyone for their contribution.