Hello Tyrone2556,
no i dont have an open where
below my full script:
LOAD
Date(Date#(num(DATA_DAY),'YYYYMMDD'),'DD/MM/YYYY') as DATA_DAY,
IQAMA_TYPE,
ID_NUMBER,
SERV_NO,
USER_STATUS,
USER_CHANNEL,
ACTIVE_OPER_ID,
ACTIVE_LINE_OPERATOR,
CHURN_OPER_ID,
DEACTIVE_CHANNEL,
DEACTIVE_LINE_OPERATOR,
OWNER_TYPE,
DEALER_NAME,
ACTIVE_DEPT_ID,
CREATE_DATE,
ACTIVE_DATE,
EXPIRY_DATE,
VOICEPAYGO,
SMSPAYGO,
DATA_PAYGO,
CONT_PAYGO,
SUBSCRIPTIONS,
ONETIMEFEE,
TOTAL_REVENUE,
TOTAL_RECHARGE,
COUNT_RECHARGE,
REMARK,
REASON
FROM [lib://Sadeem (lebara_qlik.admin)/deactivation.qvd]
(qvd);
QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AUTOGENERATE (12);
Temp:
Load
min("DATA_DAY") as minDate,
max("DATA_DAY") as maxDate
Resident deactivation;
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
MasterCalendar_deactivation :
Load
TempDate AS "DATA_DAY",
date( floor(Timestamp(TempDate, 'MM/DD/YYYY hh:mm:ss TT')),'DD/MM/YYYY') as "Date",
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
// YeartoDate(TempDate)*-1 as CurYTDFlag,
// YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
// inmonth(today(),TempDate,0)*-1 as MTD_FLAG,
// InMonthToDate( TempDate,AddYears(($(vDateToday)),-1),0)*-1 as LYMTD_FLAG,
If( TempDate - YearStart(TempDate) <= Today() - YearStart(Today()), 1, 0 ) as IsInYTD,
if( Day(TempDate)= today(),
if(month(TempDate) = Month(today()) and Day(TempDate) <= Day(today()),1,0) ,
if(month(TempDate) = Month(today()) and Day(TempDate) <= Day(today()-1),1,0)) as IsInMTD,
if(month(TempDate) = Month(today()) and Day(TempDate) <= Day(today()-1),1,0) as IsInLYMTD,
// InMonthToDate(TempDate,$(vDateToday),-1,4)*-1 as LMTD_FLAG,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
// num(Year(TempDate) )+ num( Month(TempDate)) as "MonthYear Num",
num(Year(TempDate) )& num( Month(TempDate),'00') as "MonthYear Num",
num(Year(TempDate) )& num( Month(TempDate),'00')& num( Day(TempDate),'00') as "MonthYearDay Num",
num(Year(TempDate) )& num( Month(TempDate),'00')& num( Week(TempDate),'00') as "MonthYearWeek Num",
num(Year(TempDate)-1 )& num( Month(TempDate),'00') & num( Day(TempDate),'00') as "LYMTD_Date_Num",
// floor( date#( date( floor(Timestamp(TempDate, 'MM/DD/YYYY hh:mm:ss TT')),'YYYYMMY'),'YYYYMMY')) as "MonthYear Num",
// ( date(TempDate, 'YYYYMM')) as "MonthYear Num",
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
Resident TempCalendar
Order By TempDate ASC;
Join
load
1 as "MAx MTD",
date(max("Date"),'DD/MM/YYYY') as "MAX MTD Date",
"MonthYear Num"
Resident
MasterCalendar_deactivation
where IsInMTD = 1
Group by "MonthYear Num";
Drop Table TempCalendar;
store deactivation into [lib://Sadeem (lebara_qlik.admin)/deactivation.qvd](qvd);
store MasterCalendar_deactivation into [lib://Sadeem (lebara_qlik.admin)/MasterCalendar_deactivation .qvd](qvd);
exit script;
