Qlikview String Concat

[#1]

Hi Experts,

I have the below scenario, request your guidance please.

Requirement:

To get Minumum Task Due date  ( as NextTaskDueDate) for the open tasks and it’s corresponding NextTask which is concat of TaskAction&” “&TaskCategory&” “&TaskItem

Details:

-        There is a task table as below:

  Tasks_View:LOAD AutonumberHash128( ReferralRef & CommonRef ) as %RefComKey,
1 as TaskFlag,
     TaskID,
     TaskEditDate,
     Action as TaskAction,
     Item as TaskItem,
     Category as TaskCategory,
     Team as TaskTeam,
     CreatedDate as TaskCreatedDate,
     CompletedDate as TaskCompletedDate,
     IsPublic as TaskIsPublic,
     CompletionReason as TaskCompletionReason,
   Date( floor(DueDate) ) as TaskDueDate,
      DueDate,
     Delayed as TaskDelayed,
     DelayEditBy,
     TaskStatus, WorkingDays as TaskWorkingDays,
     NetWorkDays(date(floor(CreatedDate)),date(floor(TaskEditDate))) as TaskPostworkingdays
   FROM$(vQVDPath)\Tasks_View.qvd(qvd) where Exists(CommonRef) and Exists(ReferralReference, ReferralRef) ;

NoConcatenate

Option1:

In the Data model create the below summary table

Tasks_NextAction:Load
      AutonumberHash128( ReferralRef & CommonRef ) as %RefComKey,
      if (TaskStatus='Open', date(min(DueDate)),'N/A') as NextActionDate,
      taskAction as NextTaskAction,
      Category as NextTaskCategory,
      Item as NextTaskItem Resident Tasks_View where Exists(CommonRef) and Exists(ReferralReference, ReferralRef)group by AutonumberHash128( ReferralRef & CommonRef ), taskAction,Category,Item;

But the Summary table above does not populate the NextActionDate and NextTakAction

Option2: ( Create expressions without changing the data model)

Expression for Next Action Date:

=aggr(date(min({<TaskStatus={'Open'}>} DueDate)),CommonRef,ReferralRef)

How to get the corresponding Next Task which is concat of TaskAction&” “&TaskCategory&” “&TaskItem?

[effinty2112]

Hi Satikata,

In the UI you can try:

A straight table:

TaskID	TaskDueDate	NextTask
{2F3AA591-B32E-E411-A082-E61F1356E447}	01/09/2014	Resolve Non-Reportable

where TaskID is the calculated dimension

=Aggr(if(TaskDueDate = min(TOTAL {$<TaskStatus = {'Open'}>}TaskDueDate) and TaskStatus = 'Open', TaskID),TaskID)

in dimension tab check the option to suppress when value is null.

Or in script try:

Data:

LOAD DueDate,

     TaskAction,

     TaskCategory,

     TaskCreatedDate,

     TaskDelayed,

     TaskDueDate,

     TaskEditDate,

     TaskFlag,

     TaskID,

     TaskIsPublic,

     TaskItem,

     TaskPostworkingdays,

     TaskStatus,

     TaskTeam,

     TaskWorkingDays,

     TaskCompletionReason

FROM

[Sample Task Details.xlsx]

(ooxml, embedded labels, table is Sheet1);

NextTask:

LOAD

TaskID,

TaskDueDate,

TaskAction& ' ' &TaskCategory&' ' &TaskItem as NextTask

Resident Data Where TaskStatus = 'Open';

Inner Join(NextTask)

LOAD

Min(TaskDueDate) as TaskDueDate

Resident NextTask;

Drop Field TaskDueDate from NextTask;

Good luck

Andrew