Solved

[microwin88x]

Solved

[Anonymous]

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