Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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?
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