Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

kakaderanjit53
Contributor III

Looking for help to resolve the error in the Qliksense Script

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;

Tags (1)
1 Reply
kakaderanjit53
Contributor III

Re: Looking for help to resolve the error in the Qliksense Script

Hi community,

Finally got the solution to myself.,

i.e. EXIT FAIL SCRIPT; is purposefully added for error msg

but there is no reply from anyone QlikCommunity Member.