Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
please i need your help as im facing below problem
when i try to load below script :
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;
thanks in advance,
Hi Sadeem, do you perhaps have an open Where clause in your resident load from table deactivation?
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;
Seems varMaxDate variable is not having value,
Can you debug by executing the script till drop table temp
Hi its the date format.
You need to set your date format at the start of the script.
SET DateFormat='DD/MM/YYYY';
This fixed it for me.