Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

bsrravikumar
New Contributor III

Help in Script

Hi All,

Requirement :

.Today-Coursedate>Refreshperiod , 'Expired'
.Today-Coursedate<=Refreshperiod , 'Completed'
.Coursedate is null = 'Enrolled'


.For any Engineer if any course status for a Program is having atleast one expired status Program status for that program will be expired
.For any Engineer If all course status for a Program is completed then only program status for that program is completed
.For any Engineer If Course status for a Program is having combination of only Enrolled and Completed then program status for that program is Enrolled

This is my script

T1:

LOAD [ENGINEER],[PROGRAM]

from ...

T2:

LEFT Join(T1)

// preceeding load

Load * , if( Indicator =1, 'Enrolled',if(Difference> REFRESHPERIOD,'Expired',
if(Difference <= REFRESHPERIOD,'Completed',PROGSTATUS))) as CourseStatus;

Load [ENGINEER],Course,CourseStatus,CourseDate,Refreshperiod,

if(IsNull(datemodified) or datemodified = '-' or datemodified = '' or datemodified = ' ',1,0) as Indicator,

date(Today(),'MM/DD/YYYY')-date(datemodified,'MM/DD/YYYY') as Difference

from ..

Left Join(T1)

load distinct PROGRAM ,CourseStatus as ProgramStatusExipred
Resident T1 where CourseStatus = 'Expired' and not IsNull(PROGRAM);

load distinct PROGRAM as ProgramExpired
Resident T1 where CourseStatus = 'Expired' and not IsNull(PROGRAM);

Left Join(T1)
load distinct PROGRAM ,CourseStatus as ProgramStatusEnrolled
Resident T1 where not Exists(ProgramExpired,PROGRAM) and CourseStatus = 'Enrolled'  and not IsNull(PROGRAM);

final:
load *, if(ProgramStatusExipred='Expired','Expired',if(ProgramStatusEnrolled='Enrolled','Enrolled',CourseStatus )) as ProgramStatus Resident T1;

drop table T1;

With this logic I got table dat as below.

Engineer Program ProgramStatus Course CourseStatus CourseDate Refreshperiod
ABC P1 Expired C1.1 Expired 11/20/2017 0
ABC P1 Expired C1.2 Completed 11/21/2017 33
ABC P1 Expired C1.3 Completed 11/22/2017 45
ABC P2 Expired C2.1 Expired 11/23/2017 0
ABC P2 Expired C2.2 Completed 11/24/2017 60
ABC P2 Expired C2.3 Completed 11/25/2017 80
ABC P3 Enrolled C3 Completed 11/26/2017 90
BCD P1 Expired C1.1 Completed 11/27/2017 33
BCD P1 Expired C1.2 Completed 11/28/2017 45
BCD P1 Expired C1.3 Expired 10/20/2017 0
BCD P2 Expired C2.1 Completed 11/30/2017 60
BCD P2 Expired C2.2 Completed 12/1/2017 80
BCD P3 Enrolled C3 Completed 12/2/2017 90
BCD P3 Enrolled C3.1 Completed 12/3/2017 45
CDE P1 Expired C1.1 Completed 12/4/2017 1095
CDE P1 Expired C1.2 Completed 12/5/2017 60
CDE P1 Expired C1.3 Completed 12/6/2017 80
CDE P2 Expired C2.1 Completed 12/7/2017 90
CDE P2 Expired C2.2 Completed 12/8/2017 1095
CDE P3 Enrolled C3 Completed 12/9/2017 60
CDE P3 Enrolled C3.1 Completed 12/10/2017 80
CDE P3 Enrolled C3.2 Completed 12/11/2017 1095
CDE P3 Enrolled C3.3 Enrolled
DEF P1 Expired C1.1 Completed 12/4/2017 1095
DEF P1 Expired C1.2 Completed 12/5/2017 60
DEF P1 Expired C1.3 Completed 12/6/2017 80
DEF P2 Expired C2.1 Completed 12/7/2017 90
DEF P2 Expired C2.2 Completed 12/8/2017 1095

However I need Program status independent of Resource. Plz help

EngineerProgramProgramStatusCourseCourseStatusCourseDateRefreshperiod
ABCP1ExpiredC1.1Expired11/20/20170
ABCP1ExpiredC1.2Completed11/21/201733
ABCP1ExpiredC1.3Completed11/22/201745
ABCP2ExpiredC2.1Expired11/23/20170
ABCP2ExpiredC2.2Completed11/24/201760
ABCP2ExpiredC2.3Completed11/25/201780
ABCP3CompletedC3Completed11/26/201790
BCDP1ExpiredC1.1Completed11/27/201733
BCDP1ExpiredC1.2Completed11/28/201745
BCDP1ExpiredC1.3Expired10/20/20170
BCDP2CompletedC2.1Completed11/30/201760
BCDP2CompletedC2.2Completed12/1/201780
BCDP3CompletedC3Completed12/2/201790
BCDP3CompletedC3.1Completed12/3/201745
CDEP1CompletedC1.1Completed12/4/20171095
CDEP1CompletedC1.2Completed12/5/201760
CDEP1CompletedC1.3Completed12/6/201780
CDEP2CompletedC2.1Completed12/7/201790
CDEP2CompletedC2.2Completed12/8/20171095
CDEP3EnrolledC3Completed12/9/201760
CDEP3EnrolledC3.1Completed12/10/201780
CDEP3EnrolledC3.2Completed12/11/20171095
CDEP3EnrolledC3.3Enrolled
DEFP1CompletedC1.1Completed12/4/20171095
DEFP1CompletedC1.2Completed12/5/201760
DEFP1CompletedC1.3Completed12/6/201780
DEFP2CompletedC2.1Completed12/7/201790
DEFP2CompletedC2.2Completed12/8/20171095

2 Replies

Re: Help in Script

However I need Program status independent of Resource.


Do you mean the program status should be in another table?  If so, you can create the Keyfield in your original table, and do the resident of that table with Keyfield, and Program status field. If not, can you explain further?

bsrravikumar
New Contributor III

Re: Help in Script

Hi,

Program status need not to be in another table as we are deriving it from course status.

what I need is program status should vary according to resource name.

if you see in table/excel data 1

irrespective of resource program status is expired.

I need data as per table/excel data 2

Community Browser