Hello Qlik Community,
I am creating one reconcillation script for the data evaluation i.e. Qvd_Data and SQL_data,
but there is an error occuring in the debugging script,PFA the Below code and help me to solve it.
for more details of error,PFA i.e. Screenshot of the error.
Qlik_Data_Script
$(Must_Include='lib://xxxxxxxxxxPathFile.txt');
LET vQVD_Path = '$(vQvd_path_ODS_Transactions)';
LET vQVD_Name = 'PLFMWISE_BRKG_TURNOVR';
//ENTER EITHER vNoOfYears OR vFrom_Fiscal_Yr;
LET vNoOfYears = 1; //INPUT SHOULD BE > 0 eg: 3 TO GET LAST 3 FISCAL YRS + 1 CURRENT FISCAL YR
LET vFrom_Fiscal_Yr = 'NONE'; // INPUT FORMAT eg: 'FY 2013' GETS DATA FROM FISCAL YR 2013 TO TILL NOW[2017 FISCAL YR]
LET vTable_Name = 'Brokerage';
$(Must_Include='lib://D xxxxxxxGeneric_Load_Financial_year_data.qvs');
//brokerage summary for min date, max date & total brokerage
BrokerageSummary:
Load
Min(trade_date) as Min_Date,
Max(trade_date) as Max_Date,
Sum(brokerage) as Total_Brokerage
Resident Brokerage;
//store the summary data into variables
LET vFrom_Date = Peek('Min_Date',0,'BrokerageSummary');
LET vTo_Date = Peek('Max_Date',0,'BrokerageSummary');
Let vTotal_Brokerage= Peek('Total_Brokerage',0,'BrokerageSummary');
//drop the summary table
drop table BrokerageSummary;
//compute the month-wise brokerage
Brokerage_QLIK:
LOAD Left(trade_date,6) as fiscal_month,
sum(brokerage) as Total_Brokerage
Resident Brokerage
Group by Left(trade_date,6)
Order by 1;
//drop unwanted tables
drop table Brokerage;
SQL_Data_Script
LIB CONNECT TO 'xxxxxxxxx';
LET vSP_FLAG_RECO = 'PLFMWISE_BRKG_TURNOVR_RECO'; //Reconcilation SP
Brokerage_SQL:
Load *;
SQL exec qlik_ods_data $(vSP_FLAG_RECO),$(vFrom_Date),$(vTo_Date);
Reconcillation_Script
//count the no of errors
Set vErrorCount=0;
//print the header row
TRACE Brokerage Mismatch Details;
TRACE Month_SQL Month_Qlik Brokerage_SQL Brokerage_Qlik Brokerge_Difference;
TRACE ------------------------------------------------------------------------------------;
//Looping over the all the months data
For vLoop = 0 to NoOfRows('Brokerage_QLIK')-1
//extract the current month
Let vMonthSQL= Peek('Month_Name',vLoop,'Brokerage_SQL');;
Let vMonthQlik= Peek('fiscal_month',vLoop,'Brokerage_QLIK');
//extract the current month brokerage figures
LET vBrokerageSQL = fabs(Peek('brokerage',vLoop,'Brokerage_SQL'));
LET vBrokerageQlik = fabs(Peek('Total_Brokerage',vLoop,'Brokerage_QLIK'));
//compare the brokerage
Let vBrokerageDiff=fabs($(vBrokerageSQL)-$(vBrokerageQlik));
//if difference is more than 5
IF($(vBrokerageDiff)>5 ) Then
//log the error
TRACE $(vMonthSQL) $(vMonthQlik) $(vBrokerageSQL) $(vBrokerageQlik) $(vBrokerageDiff);
//increase the error count
Let vErrorCount=$(vErrorCount)+1;
END IF;
Next vLoop;
//if there is an error
If $(vErrorCount)>0 THEN
//fail the script through syntax error
EXIT FAIL SCRIPT;
exit Script;
End If;
