topic Re: Scenario Logic Help in QlikView

Scenario Logic Help

abhaysingh — Tue, 20 Oct 2015 13:28:40 GMT

Hi All,

I have two tables Community Test Data Assign and Community Test Data Assign. there are link on the basis of Visit ID,

In assign Table i have Date Submitted Field and in Visit Table i have Date Due field.

I need to calculate below 3 Logic..

1. AssignmentsCompletedCount = count of DateSubmitted >= startDate And DateSubmitted <= endDate and DateSubmitted <= Visit.DateDue

2. AssignmentsSubmittedLateCount = Count of DateSubmitted >= startDate And DateSubmitted <= endDate and DateSubmitted > Visit.DateDue

3. AssignmentsOverdueCount = Count of Visit.DateDue + Visit.GracePeriod >= startDate && Visit.DateDue + Visit.GracePeriod <= endDate && DateSubmitted == null

Start Date and End Date user will enter..

I need the count of Assignment Completed, Assignement Submitted late, Assignment Overdue in Pie chart..

Can any one help me in this??

thanks

Re: Scenario Logic Help

abhaysingh — Tue, 20 Oct 2015 13:57:34 GMT

Pls revert..thanks

Re: Scenario Logic Help

abhaysingh — Wed, 21 Oct 2015 07:54:56 GMT

hey guys any one pls suggest??

Re: Scenario Logic Help

Anonymous — Wed, 21 Oct 2015 10:06:54 GMT

Is this flag IsDeleted useful for this?

Re: Scenario Logic Help

abhaysingh — Wed, 21 Oct 2015 10:06:58 GMT

if(AssignmentTask = 'Completed' , count({< DateSubmitted = {'>=$(=vStartDate) <=$(=vEndDate)'} >} AssignmentTask),

if(AssignmentTask = 'Submitted Late' , count({< DateSubmitted = {'>=$(=vStartDate) <=$(=vEndDate)'} >} AssignmentTask),

if(AssignmentTask = 'Assingment Overdue' , count({< AssmntOverdueDate = {'>=$(=vStartDate) <=$(=vEndDate)'} >} AssignmentTask))))

Re: Scenario Logic Help

abhaysingh — Wed, 21 Oct 2015 10:07:44 GMT

yes False or null to be pick

Re: Scenario Logic Help

Anonymous — Wed, 21 Oct 2015 10:13:59 GMT

Abhay,

As per this logic

if(num(DateSubmitted)<=num(DateDue),'Completed',

if(num(DateSubmitted)>num(DateDue),'SubmittedLate',

if(IsNull(num(DateSubmitted)),'Overdue'))) as AssignmentStatus,

No null values in case of Overdue...

Tell me what expression you are using now with these status?

Does I need to create grace period as well? On what basis you are creating grace period?

Re: Scenario Logic Help

Anonymous — Wed, 21 Oct 2015 10:15:23 GMT

oky, share your expression

Re: Scenario Logic Help

Anonymous — Wed, 21 Oct 2015 10:20:44 GMT

Got it.

Now if your Overdue is not matching then check the joining part of the table whether it wud be outer or right or left...

as your completed and late status task are matching.

Re: Scenario Logic Help

Anonymous — Wed, 21 Oct 2015 10:22:26 GMT

I am creating some null values for overdue and wud be sharing with you in a while...

Re: Scenario Logic Help

Anonymous — Wed, 21 Oct 2015 10:26:31 GMT

Also check whether DataSubmitted is null or a blank value!!!

This may mismatch the values of OverDue

Re: Scenario Logic Help

Anonymous — Wed, 21 Oct 2015 10:39:35 GMT

AssmntOverdueDate??

How you are creating?? this may be the another reason of your mismatch of values...

Re: Scenario Logic Help

Gabriel — Wed, 21 Oct 2015 12:20:25 GMT

Hi,

May be this will help. I created the extra field in the script as Task Status then in the UI just use SET ANALYSIS to count VisitId where Task Status = 'Submitted In Time' or 'Late Submission' or 'Task OverDue'

TestAssigned:

LOAD VisitId,

DateSubmitted,

SurveyTaskId,

IsDeleted

FROM

[Community Test Data Assgn.xlsx]

(ooxml, embedded labels, table is Sheet2);

LEFT JOIN(TestAssigned)

LOAD

VisitId,

DateDue

FROM

[Community Test data visit.csv.txt]

(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Final_Task_Data:

load

IF(DateSubmitted <= DateDue, 'Task Submitted In Time',

IF(DateSubmitted > DateDue , 'Late Submission',

IF(ISNULL(DateDue) OR LEN(DateDue) = 0, 'Task OverDue'))) AS [Task Status]

Resident TestAssigned

;

DROP Table TestAssigned;

Hope this helps