Concat - limit returned Rows to newest row only

[#1]

Anyone know how to limit this to 1 returned row?  -  the newest one (based on WORK_LOG_SUBMIT_DATE)?

= concat( { <WORK_LOG_TYPE = {'Customer Status Update'  }>*<Follow_Up_Required = {'Yes'}>* < Company -= {'Company A' }>*<[Ticket Status]={'Open' }>}  distinct   [Incident Number] &' | '& Company &' | '& subfield(Site, ':',2) &' | '&'Pri:'& Priority &' | '& left(Description,95)&' | '& 'Submit Date:'&[Submit Date]&' | ' &'Assignee:'& Assignee &' | ' &'AgeDays:'& num(AgeDaysExact,'##.##')& chr(13)&'Next Steps:  '   &  SPLIT_SUBMITTER &'  -  '& WORK_LOG_SUBMIT_DATE & chr(13) & DETAILED_DESCRIPTION  ,chr(13)&'________________'&chr(13)&'________________'&chr(13) )

When this executes it returns 2 or more rows because  these 3 fields below can have more than 1 value for the record ( all the rest are the same for each record but because of this, "Distinct" does not limit returned rows to one 😞

SPLIT_SUBMITTER       WORK_LOG_SUBMIT_DATE        DETAILED_DESCRIPTION

Any help much appreciated.

Jim

[#2]

Use Only function. Instead of SPLIT_SUBMITTER use Only(SPLIT_SUBMITTER).

Regards,

Kiran.

[#3]

Thanks Kirin - tried it, but once I introduce an "only" or a "max" to any field it returns nothing.

[#4]

Try adding the below set analysis to your expression:

Only({<WORK_LOG_SUBMIT_DATE={"$(=max(WORK_LOG_SUBMIT_DATE))"}>}SPLIT_SUBMITTER)

Kiran.

[#5]

Kirin am I doing this right? got nothing back

= concat ( { <WORK_LOG_TYPE = {'Customer Status Update'  }>*<Follow_Up_Required = {'Yes'}>* < Company -= {'Company A' }>*<[Ticket Status]={'Open' }>*<WORK_LOG_SUBMIT_DATE>}  Only({<WORK_LOG_SUBMIT_DATE={"$(=max(WORK_LOG_SUBMIT_DATE))"}>}SPLIT_SUBMITTER) * (  [Incident Number] )&' | '& Company &' | '& subfield(Site, ':',2) &' | '&'Pri:'& Priority &' | '& left(Description,95)&' | '& 'Submit Date:'&[Submit Date]&' | ' &'Assignee:'& Assignee &' | ' &'AgeDays:'& num(AgeDaysExact,'##.##')& chr(13)&'Next Steps:  '   &  (SPLIT_SUBMITTER) &'  -  '& WORK_LOG_SUBMIT_DATE & chr(13) & (DETAILED_DESCRIPTION ) ,chr(13)&'________________'&chr(13)&'________________'&chr(13) )

[#6]

First of all if you are tryingto have multiple condition in set analysis you can use them with a ',' (comma) seperator instead of '>*<'. To have expression in and expression (only in concat) you need to have aggr function. But first lets fix the multiple valued functions.

We need to break this down to the three fields with multiple records. Try the below expression and check if the result is desired.

Only({<WORK_LOG_SUBMIT_DATE={"$(=max(WORK_LOG_SUBMIT_DATE))"}>} Distinct SPLIT_SUBMITTER&'|'&WORK_LOG_SUBMIT_DATE&'|'&DETAILED_DESCRIPTION ).

This would tell us for recent work_log_submit_date if these fields have one or more values. Accordingly we can modify the expression.

Can you share the document?

Kiran

[#7]

Hi Kiran,

Sorry cant share.

I tried this and any time I introduce only or max etc I get nothing back.

Jim