Hi,
Try this code, in the script, for the entire data.
Original_Data:
load * inline [
ID_TCKT ,STATUS ,YEAR ,MONTH_STATUS ,CREATE_DATE ,CREATE_TIME ,MONTH_CALENDAR
TCKT05 ,In Progress ,2016 ,Jul ,15/07/2016 ,14:29:33 ,Jul
TCKT05 ,In Progress ,2016 ,Aug , , ,Jul
TCKT05 ,In Progress ,2016 ,Sep , , ,Jul
TCKT05 ,Closed ,2016 ,Oct , , ,Jul
TCKT02 ,In Progress ,2016 ,May ,02/05/2016 ,19:51:00 ,May
TCKT02 ,In Progress ,2016 ,Jun , , ,May
TCKT02 ,Completed ,2016 ,Jul , , ,May
TCKT02 ,Completed ,2016 ,Aug , , ,May
TCKT02 ,Closed ,2016 ,Sep , , ,May
TCKT02 ,Closed ,2016 ,Oct , , ,May
TCKT03 ,Planned ,2016 ,May ,20/05/2016 ,02:45:53 ,May
TCKT03 ,Planned ,2016 ,Jun , , ,May
TCKT03 ,Closed ,2016 ,Jul , , ,May
TCKT03 ,Closed ,2016 ,Aug , , ,May
TCKT03 ,Closed ,2016 ,Sep , , ,May
TCKT07 ,In Progress ,2016 ,Oct ,15/10/2016 ,12:35:48 ,Oct];
NoConcatenate
Temp:
LOAD ID_TCKT,
STATUS,
YEAR,
MONTH_STATUS,
CREATE_DATE,
CREATE_TIME,
MONTH_CALENDAR,
text(date(addmonths(date#(YEAR&MONTH_CALENDAR,'YYYYMMM'),-1),'YYYYMMM')) as CALENDAT_PREVIOUS
Resident Original_Data;
drop table Original_Data;
left join(Temp)
LOAD YEAR&MONTH_STATUS as CALENDAT_PREVIOUS,
count(DISTINCT ID_TCKT) as Count_needed
Resident Temp
where STATUS<>'Closed'
group by YEAR&MONTH_STATUS;
Best regards,
Cosmina
