Extract last element in a string array

Question & Answer
, ,
1

I have an address field for which I am trying to extract the country during data preparation. For example:
123 West St, Washington, DC 20005, USA
or
100 Main St STE 700, Office 754, Washington, DC 20004, USA
Note that they don’t have the same number of elements in the address. I want to extract that last element in order to get the country.

I have tried using:
split(
{address},
“,”,
strlen({address})-strlen(replace({address}, “,” ,“” ))
)
however, I get an error when trying to use the strlen/replace combination in the split function in data preparation.

I have no control over the form of the address I receive, so have to deal with it as is.

Any suggestions for how I extract that last element in a string?

2

Hi @JMthinkrf
i tried you formula with one string you provided.

grafik
grafik867×317 42.6 KB

BR

1 Like
3

It works for me too, you need to +1 at the end, then it should work:
split(
{1},
‘,’,
strlen({1})-strlen(replace({1}, ‘,’ ,‘’ ))+1
)

4

Thank you both. There must be something odd with my dataset. When I try with a static string like in your code above, it works fine. But when I try it substitute with the actual field name {address}, and then try to publish it, I get

image
image853×58 4.25 KB

{address} is type string. The formula works fine when I use:
split(
{address},
“,”,
4
)

I’ll keep trying stuff. Thanks again for the advice.

5

Do you try on dataset or analysis level? could you test the other option?

6

Hi @JMthinkrf Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community Q&A Forum!

7

Unfortunately, no it did not work in a calculated field during data preparation. I didn’t get an error when I used a static string like in ErikG’s example, but when I substituted that static string with a string field, I got an error.

I ended up using nested ifelse statements to extract the last field based upon the number of commas:

ifelse(isnull(split(address,“,”,7)),
ifelse(isnull(split(address,“,”,6)),
ifelse(isnull(split(address,“,”,5)),
ifelse(isnull(split(address,“,”,4)),
ifelse(isnull(split(address,“,”,3)),
trim(split(address,“,”,2)),
trim(split(address,“,”,3))
),
trim(split(address,“,”,4))
),
trim(split(address,“,”,5))
),
trim(split(address,“,”,6))
),
trim(split(address,“,”,7))
)

Not terribly elegant, but it worked.