1 Reply Latest reply: Feb 3, 2017 3:01 AM by RANJIT KAKADE RSS

    Looking for help to resolve the error in the Qliksense Script

    RANJIT KAKADE

      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;