Extract numbers from account name

Questions & Answers
1

Hi all,

How can I extract the last 2 or 3 characters from the Tempo Account name in EazyBI?

Example: the Tempo Account Name is “CUSTOMER - JUN22 - 100” or “CUSTOMER - JUN22 - 20”
I want to extract the numbers “100” and “20” into a new numeric field.

I saw that I can extract from Measures but is it also possible from the Tempo Account name?

I hope you can help me. Thanks a lot.

Stefan - Getnoticed

2

@Getnoticed

Try creating a new calculated measure using regular expressions:

case WHEN
[Account].CurrentMember.Level.name = "Account"
THEN
Cast(ExtractString(
--account name without extra spaces
 Replace(
  Replace(
    [Account].CurrentMember.GetCaption, 
    "("|| [Account].CurrentMember.Key || ")", 
    ''
  ),
  " - ",
  " "
 ),
  
 " (\d+)",
 1
) as integer)
END

Martins / eazyBI

image
image2094×862 146 KB

1 Like
3

Thanks a lot Martins, that works great for me!