split a field into different fields

[#1]

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?

[its_anandrjs]

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

];

[#3]

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

[Miguel_Angel_Baeyens]

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

[#5]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[#7]

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

[#8]

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
];

[its_anandrjs]

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.

[MarcoWedel]

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