How to create a big Concat string with values.....

[#1]

Hi Folks

I have a requirement to create a single string in a text box that looks like a grid of data, so it will have a TransactionDate, Sum(Value) for that Date, Sum(Value2) for that date, followed by a line feed and then the next date. The purpose is to be able to embed this information into an email that will be sent as an alert.

So far:

ConCat(DISTINCT Date(TradeDate,'DD/MM/YY'),chr(10)) gives me a list of dates separated by line feeds

Concat(Aggr(Sum(Value1),TradeDate),chr(10)) gives me a list of the sum of value1 for each date, separated by line feeds

Concat(Aggr(Sum(Value2),TradeDate),chr(10)) gives me a list of the sum of value2 for each date, separated by line feeds

What I need though is to join these as one string, so was thinking of something like this:

Concat(

     DISTINCT Date(TradeDate,'DD/MM/YY')&

     ';   Value 1 = ' & Aggr(Sum(Value1),TradeDate)&

     ';   Value 2 = ' & Aggr(Sum(Value2),TradeDate)

     ,chr(10)

)

This is "almost" there, but what I get is each date twice, the first time with values and the second time without values and I'm wondering what the cause of this is and what it the potential solution to it. See picture below:

Any help would be appreciated.

Cheers,

Nigel.

[danielrozental · Accepted solution]

Try something like this

Concat(DISTINCT

     Aggr(Date(TradeDate,'DD/MM/YY')&

          ';   Value 1 = ' & num(Sum(Value1),'#,##0.00','.',',') &

          ';   Value 2 = ' & num(Sum(Value2),'#,##0.00','.',',') &

          chr(10)

     ,TradeDate)

)