Try this code:
Temp:
LOAD * Inline
[
ID,Status,RenewDT,CancelDT
1,A,2016/03,
2,A,2016/06,
3,A,2017/01,
4,A,2017/01,
5,A,2017/02,
6,C, ,2017/01
7,C, ,2017/01
8,C, ,2017/02
];
NoConcatenate
Temp2:
LOAD
ID,Status,RenewDT as Period,1 as DTStatus
Resident Temp
where len(RenewDT)>0;
LOAD
ID,Status,CancelDT as Period,2 as DTStatus
Resident Temp
where len(CancelDT)>0;
Drop Table Temp;
stage1:
LOAD count(ID) as Renewed
,Period
Resident Temp2
where Status = 'A'
Group by Period;
Left Join
LOAD count(ID) as Cancel
,Period
Resident Temp2
where Status = 'C'
Group by Period;
Left Join
LOAD count(ID) as TotalID
,Period
Resident Temp2
Group by Period;
DROP Table Temp2;
