topic Concat - limit returned Rows to newest row only in QlikView

Concat - limit returned Rows to newest row only

Wed, 26 Oct 2011 03:16:25 GMT

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

Concat - limit returned Rows to newest row only

Wed, 26 Oct 2011 08:58:57 GMT

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

Regards,

Kiran.

Concat - limit returned Rows to newest row only

Wed, 26 Oct 2011 13:09:58 GMT

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

Concat - limit returned Rows to newest row only

Wed, 26 Oct 2011 13:14:22 GMT

Try adding the below set analysis to your expression:

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

Kiran.

Concat - limit returned Rows to newest row only

Wed, 26 Oct 2011 13:50:00 GMT

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) )

Concat - limit returned Rows to newest row only

Wed, 26 Oct 2011 14:11:38 GMT

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

Concat - limit returned Rows to newest row only

Fri, 28 Oct 2011 23:46:14 GMT

Hi Kiran,

Sorry cant share.

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

Jim