Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
];
what does the 1 mean in (sources, '_', 1) ?
Subfield() extracts
In your case, the first position. If you specify 2 instead you will get "florida" and "georgia".
Miguel
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
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
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
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
];
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.
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