Qlikview String Concat

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Qlikview String Concat

effinty2112 — Fri, 01 Sep 2017 07:36:42 GMT

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

topic Qlikview String Concat in QlikView

Qlikview String Concat

Re: Qlikview String Concat