Hi,
this is my script and data was stored in day wise.
Added new field in sql and qvd also reloaded but below is getting error because it is fetch the data from each qvd.
field name : esim_subcategory
LIB CONNECT TO 'Qlik_AWS_Vertica (telenormm_qlikadmin)';
Let QVD_Path = 'lib://QVD_folder_Path (telenormm_qlikadmin)/Activation_and_Subscription_base_Report_fnl';
//Let varMinDate = Num(Date(DATE#('20210101','YYYYMMDD')));
Let varMinDate = Num(date(monthname(addmonths(today(),-14)),'YYYYMMDD')); // it's take last 14 months data from 1st date of 14th Month
Let varMaxDate = Num(Date(Today()));
// Trace Min Date :$(varMinDate) Max Date : $(varMaxDate);
TempCalendar:
LOAD
// rprt_dt as TempDate
// from [$(QVD_Path)\QVD_dt_r_dormancy_dly.qvd](qvd);
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo()-1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo()-1 <= $(varMaxDate);
//Data starts laoding
for i= 0 to NoOfRows('TempCalendar')-1
//variable to pick all new dates one by one from source which is saved in dt table
let dt = date(peek('TempDate',$(i),'TempCalendar'),'YYYYMMDD');
let mnth = Month(date(peek('TempDate',$(i),'TempCalendar'),'YYYYMMDD'));
let yr = year(date(peek('TempDate',$(i),'TempCalendar'),'YYYYMMDD'));
Trace data loading for the date: $(dt);
Let vQVDcheck = not isnull(QvdCreateTime('[$(QVD_Path)\$(yr)\$(mnth)\r_actvtn_sbscrptn_$(dt).qvd]'));
if $(vQVDcheck) = -1 then
r_actvtn_sbscrptn:
LOAD
Date("rprt_dt") as Date,
MonthName(rprt_dt) As "Month Year",
Year(rprt_dt) As Year,
Month(rprt_dt) As Month,
QuarterName(rprt_dt) as QuarterName,
'Q' & CEIL(NUM(MONTH("rprt_dt"))/3) as Quarter,
'Q' & CEIL(NUM(MONTH("rprt_dt"))/3) & ',' & Year("rprt_dt") AS "Quarter Year",
if(IsNull("ln_of_bssnss"),'Unknown',"ln_of_bssnss") as "Line of Business",
if(IsNull("prdct_ln"),'Unknown',"prdct_ln") as "Product Line",
if(IsNull("cstmr_typ_desc"),'Unknown',"cstmr_typ_desc") as "Customer Type",
if(IsNull("lf_cycl_stts_desc"),'Unknown',"lf_cycl_stts_desc") as "Customer Status (Life Cycle)",
if(IsNull("cbs_stts_desc"),'Unknown',"cbs_stts_desc") as "cbs_stts_desc",
if(IsNull("cstmr_sgmnt_desc"),'Unknown',"cstmr_sgmnt_desc") as "Customer Segment",
if(IsNull("prmry_offrng_nme"),'Unknown',"prmry_offrng_nme") as "Price Plan",
if(IsNull("prtnr_typ_desc"),'Unknown',"prtnr_typ_desc") as "Partner Type",
//if(IsNull("prtnr_code"),'Unknown',"prtnr_code") as "Partner Code",
//if(IsNull("prnt_prtnr_code"),'Unknown',"prnt_prtnr_code") as "DTR Code", //"Parent Partner Code"
// "bll_invc_dt" as "Bill Invoice Date",
if(IsNull("emply_sim_indctr"),'Unknown',"emply_sim_indctr") as "Employee Sim Indicator",
if(IsNull("clstr_nme_mst_usd_ovrll"),'Unknown',"clstr_nme_mst_usd_ovrll") as "Cluster - Most Used",
if(IsNull("mn_clstr_nme_mst_usd_ovrll"),'Unknown',"mn_clstr_nme_mst_usd_ovrll") as "Mini Cluster - Most Used",
// if(IsNull("cllst_nme_mst_usd_ovrll"),'Unknown',"cllst_nme_mst_usd_ovrll") as "Cell Site ID Most Used",
if(IsNull("rgn_nme_mst_usd_ovrll"),'Unknown',"rgn_nme_mst_usd_ovrll") as "Region - Most Used",
// if(IsNull("clstr_nme_pos_wse_lctn"),'Unknown',clstr_nme_pos_wse_lctn) as "Cluster - POS",
// if(IsNull("mn_clstr_nme_pos_wse_lctn"),'Unknown',mn_clstr_nme_pos_wse_lctn) as "Mini Cluster - POS",
// if(IsNull("rgn_nme_pos_wse_lctn"),'Unknown',rgn_nme_pos_wse_lctn) as "Region - POS",
// if(IsNull("cllst_nme_pos_wse_lctn"),'Unknown',cllst_nme_pos_wse_lctn) as "Cell Site ID POS",
"ftd_new_actv_cnt" as "Activation Count",
"ftd_rjnrs_cnt" as "Rejoiners Count",
"ftd_actv_cnt" as "Active Subscribers Count",
"ftd_chrn_cnt" as "Churn Count",
"ftd_dscnnctn_cnt" as "Disconnected Users Count",
"ftd_grss_chrn_cnt" as "Gross Churn Count",
"ftd_pool_stt_cnt" as "Pool Status Count",
"ftd_idle_stt_cnt" as "Idle Status Count",
"ftd_net_add_cnt" as "Net Addition Count",
"ftd_net_chrn_cnt" as "Net Churn Count",
"ftd_opnng_cnt" as "Opening Count",
"ftd_clsng_cnt" as "Closing Count",
esim_subcategory as eSIM_SubCategory
// load_date,
// load_user,
// execution_id
from [$(QVD_Path)/$(yr)/$(mnth)/r_actvtn_sbscrptn_$(dt).qvd](qvd);
//from [$(QVD_Path)/2023/Oct/r_actvtn_sbscrptn_20231004.qvd](qvd);
else
Trace QVD not exists for the date : $(dt);
End if;
let i=i+0;
Next
/*
All will be replaced with FACT_SUBSCRIPTION_SUMMARY_DAILY measures
Activations - distinct cnt of subscription id where evnt_grss_add=1
Active - distinct count of subcription id where LFE_CYCL_STTUS_TYP_CD=1
Churn - distinct count of subcription id where EVNT_CHRN=1
Disconnections - distinct count of subcription id where L"Customer Status (Life Cycle)"="Disconnections" [note:given desc since unaware of LFE_CYCL_STTUS_TYP_CD; desc has mentioned FTD_Disconnect]
Gross Churn - Churn+Disconnections
Pool - distinct count of subcription id where L"Customer Status (Life Cycle)"="Pool" [note:given desc since unaware of LFE_CYCL_STTUS_TYP_CD
Idle - distinct count of subcription id where L"Customer Status (Life Cycle)"="Idle" [note:given desc since unaware of LFE_CYCL_STTUS_TYP_CD
Netadds - distinct count of subcription id where
Net churn - distinct count of subcription id where
closing base - distinct count of subcription id where
Opening Base - distinct count of subcription id where
Rejoiners - distinct count of subcription id where L"Customer Status (Life Cycle)"="Rejoiner" [note:given desc since unaware of LFE_CYCL_STTUS_TYP_CD
*/
