Help in Script

[Anonymous]

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

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	Completed	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	Completed	C2.1	Completed	11/30/2017	60
BCD	P2	Completed	C2.2	Completed	12/1/2017	80
BCD	P3	Completed	C3	Completed	12/2/2017	90
BCD	P3	Completed	C3.1	Completed	12/3/2017	45
CDE	P1	Completed	C1.1	Completed	12/4/2017	1095
CDE	P1	Completed	C1.2	Completed	12/5/2017	60
CDE	P1	Completed	C1.3	Completed	12/6/2017	80
CDE	P2	Completed	C2.1	Completed	12/7/2017	90
CDE	P2	Completed	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	Completed	C1.1	Completed	12/4/2017	1095
DEF	P1	Completed	C1.2	Completed	12/5/2017	60
DEF	P1	Completed	C1.3	Completed	12/6/2017	80
DEF	P2	Completed	C2.1	Completed	12/7/2017	90
DEF	P2	Completed	C2.2	Completed	12/8/2017	1095

[settu_periasamy]

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?

[Anonymous]

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