Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
microwin88x
Creator III
Creator III

Solved

Solved

1 Reply
Anonymous
Not applicable

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