Need help with calculated field expression

[Prathik]

I need help with to create an expression for a calculated field in aws quicksight. I have a column named “requester” and is of the form:

1. arn:aws:sts::700695696300:as-role/xyz.abc.hgd-123/abcxyz
2. arn:aws:sts::700695696300:as-role/xyzlmo/abcxyz
   And i want a new column with output is :
3. 700695696300-xyz.abc.hgd-123
4. 700695696300-xyzlmo.
   Basically get the number and if there are 2 slashes after as-role, then get the text after the first slash only.

I was thinking of something like (it is pseudo code kinda thing):

ifelse(
  contains({requester}, "arn:aws:sts::"),
  concat(
    extract(
      substring({requester}, 14, strlen({requester})),
      '(.+):ass-role/([a-zA-Z0-9.-]+)',
      2
    ),
    '-',
    ifelse(
      contains(
        split(
          extract(substring({requester}, 14,strlen({requester})), '(.+):as-role/([a-zA-Z0-9.-]+)', 3),
          '/'
        )[1],
        '/'
      ),
      split(
        extract(substring({requester}, 14,strlen({requester})), '(.+):as-role/([a-zA-Z0-9.-]+)', 3),
        '/'
      )[0],
      extract(substring({requester}, 14,strlen({requester})), '(.+):as-role/([a-zA-Z0-9.-]+)', 3)
    )
  ),
  ''
)

[Sanjeeb2022]

Hi @Prathik - Can you please create a calculated field with below formula.

concat(split({Data},":",5),'-',substring({Data},locate({Data},'/') +1,locate({Data},'/',locate({Data},'/') + 1) - (locate({Data},'/') + 1) )
 )

Replace the Data with your require column. See the sample output below

Regards - Sanjeeb

[Prathik]

Thank you for your help!!!