Field not found loading data from QVD

[bhaskar]

Hi,

One New field added in Sql table as the same field stored in QVD also.

while reloading QVF getting error as field not found.

QVD's are stored in Day wise and new field added 2 days back in sql table.

request to please help how to resolve.

Bhaskar

[rwunderlich]

This is generally due to the fact that field names are case sensitive in Qlik script but are case insensitive in SQL. Please post your script if you have further questions. 

-Rob

[bhaskar]

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

*/

[rwunderlich]

Do you mean esim_subcategory exists in some QVDs but not others?

-Rob

[bhaskar]

thanks for responding.

there is no column in historical QVD's

last two back added that field in SQL, QVD and   incremental QVD's reloading   successfully.