Magic ETL

Magic ETL

Help on Beast Mode Concat Format

Current I get for a Summary 2711 Meetings and $143043062 Total Est. Budget but I am looking for the $ amount to include comma separators and two decimal. Am using a beast mode of CONCAT(COUNT(`Meeting Identifier`),' Meetings and $', SUM(`Total Estimated Budget`),' Total Est. Budget') and I am a novice and painfully stuck. Any help is apprecaited. 

Best Answer

  • Domo Employee
    Answer ✓

    What we could really use here is the FORMAT() function.  @DaniBoy  (hint, hint)

     

    However, as that is not an option for beastmodes, we will have to get more creative with our solution.

     

    It's going to look something like this... but I have not had the time to test it out:

    1. CONCAT(
      COUNT(`Meeting Identifier`)
      ,' Meetings and $'
      ,left(
      round(sum(`Total Estimated Budget`),2),
      MOD(
      (LENGTH(round(sum(`Total Estimated Budget`),2))),3
      ))
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >1 then ',' else '.' end
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >1 then SUBSTRING(round(sum(`Total Estimated Budget`),2),MOD(
      (LENGTH(round(sum(`Total Estimated Budget`),2))),3
      )+1,3) else RIGHT(round(SUM(`Total Estimated Budget`),2),2) end
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >2 then ',' end
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >2 then SUBSTRING(round(sum(`Total Estimated Budget`),2),MOD(
      (LENGTH(round(sum(`Total Estimated Budget`),2))),3
      )+1,3) else RIGHT(round(SUM(`Total Estimated Budget`),2),2) end
      ,' Total Est. Budget'
      )

     

    I did get this to work with my sample data:1.png

     

Answers

  • Domo Employee
    Answer ✓

    What we could really use here is the FORMAT() function.  @DaniBoy  (hint, hint)

     

    However, as that is not an option for beastmodes, we will have to get more creative with our solution.

     

    It's going to look something like this... but I have not had the time to test it out:

    1. CONCAT(
      COUNT(`Meeting Identifier`)
      ,' Meetings and $'
      ,left(
      round(sum(`Total Estimated Budget`),2),
      MOD(
      (LENGTH(round(sum(`Total Estimated Budget`),2))),3
      ))
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >1 then ',' else '.' end
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >1 then SUBSTRING(round(sum(`Total Estimated Budget`),2),MOD(
      (LENGTH(round(sum(`Total Estimated Budget`),2))),3
      )+1,3) else RIGHT(round(SUM(`Total Estimated Budget`),2),2) end
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >2 then ',' end
      ,case when (LENGTH(round(sum(`Total Estimated Budget`),2))/3) >2 then SUBSTRING(round(sum(`Total Estimated Budget`),2),MOD(
      (LENGTH(round(sum(`Total Estimated Budget`),2))),3
      )+1,3) else RIGHT(round(SUM(`Total Estimated Budget`),2),2) end
      ,' Total Est. Budget'
      )

     

    I did get this to work with my sample data:1.png

     

  • ST_-Superman- - Endless thanks - worked like a charm. Now I just need to dive in and understand it for future use. You made me and my client happy campers. Very much apprecaited. And I have submitted FORMAT as an enhancement request as I know many others have also done. 

Welcome!

It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
Sign In