7 Replies Latest reply: Dec 12, 2017 11:56 AM by Evan Kurowski RSS

    match() clause in qlikview sql select script

    Ashley Navin

      DeferredFees:

      load Distinct

      PROD_DT

      , MSTR_ACCT_NBR

      , sum(DEFER_BAL_AMT) as Defer_fee_bal_amt

      ;

      sql select

      DW.V_COMM_LOAN_FEES_MTH.PROD_DT

      , DW.V_COMM_LOAN_FEES_MTH.MSTR_ACCT_NBR

      , DW.V_COMM_LOAN_FEES_MTH.DEFER_BAL_AMT

       

      from DW.V_COMM_LOAN_FEES_MTH

      where (DW.V_COMM_LOAN_FEES_MTH.PROD_DT = (select MAX (DW.V_COMM_LOAN_FEES_MTH.PROD_DT) from DW.V_COMM_LOAN_FEES_MTH

      where DW.V_COMM_LOAN_FEES_MTH.PROD_DT >=  {D '$(vMonthStart)'} and DW.V_COMM_LOAN_FEES_MTH.PROD_DT <= {D '$(vMonthEnd)'})) and

      match(DW.V_COMM_LOAN_FEES_MTH.NOTICE_NBR,'00011','00031','00043','00044','00045','00046')

      group by MSTR_ACCT_NBR ;

       

       

      I am using the above code trying to get the sum of the defer_bal_amt where the notice nbr is the values '00011','00031','00043','00044','00045','00046' and grouping by mstr acct nbr.

      I ran this successfully in SQL so i copy and pasted into my qlikview script and just changed my 'IN' statement in sql to a match() statement. I am getting an error for having the group by statement but i need to group by this field to sum the bal amt.


      Let me know what I can change

        • Re: match() clause in qlikview sql select script
          Anil Babu

          May be this?

           

          load Distinct

          PROD_DT

          , MSTR_ACCT_NBR

          , sum(DEFER_BAL_AMT) as Defer_fee_bal_amt

          Group By PROD_DT, MSTR_ACCT_NBR;

          sql select

          DW.V_COMM_LOAN_FEES_MTH.PROD_DT

          , DW.V_COMM_LOAN_FEES_MTH.MSTR_ACCT_NBR

          , DW.V_COMM_LOAN_FEES_MTH.DEFER_BAL_AMT

           

          from DW.V_COMM_LOAN_FEES_MTH

          where (DW.V_COMM_LOAN_FEES_MTH.PROD_DT = (select MAX (DW.V_COMM_LOAN_FEES_MTH.PROD_DT) from DW.V_COMM_LOAN_FEES_MTH

          where DW.V_COMM_LOAN_FEES_MTH.PROD_DT >=  {D '$(vMonthStart)'} and DW.V_COMM_LOAN_FEES_MTH.PROD_DT <= {D '$(vMonthEnd)'})) and

          match(DW.V_COMM_LOAN_FEES_MTH.NOTICE_NBR,'00011','00031','00043','00044','00045','00046')

          group by MSTR_ACCT_NBR ;

          • Re: match() clause in qlikview sql select script
            Evan Kurowski

            Hello Ashley,

             

             

             

             

            If you had an SQL statement that was functional in its native platform, when incorporating that statement into a QlikView script via sql select, you don't want to change anything.

             

             

             

             

            Keep the exact syntax of the original working query, and paste that directly into your script.  If it worked in a DB client, it should work the same way in the QlikView script.

             

             

            Then if you want to adjust things in QlikView, address that syntax in preceding loads.

             

             

            ~E

              • Re: match() clause in qlikview sql select script
                Ashley Navin

                sql select

                DW.V_COMM_LOAN_FEES.PROD_DT

                , sum(DW.V_COMM_LOAN_FEES.DEFER_BAL_AMT)

                , DW.V_COMM_LOAN_FEES.MSTR_ACCT_NBR

                from DW.V_COMM_LOAN_FEES where (DW.V_COMM_LOAN_FEES.PROD_DT = (select MAX (DW.V_COMM_LOAN_FEES.PROD_DT) from DW.V_COMM_LOAN_FEES))

                and (DW.V_COMM_LOAN_FEES.NOTICE_NBR IN (''00011'', ''00031'', ''00043'', ''00044'', ''00045'', ''00046''))

                and (DW.V_COMM_LOAN_FEES.DEFER_BAL_AMT <> 0)

                group by PROD_DT, MSTR_ACCT_NBR

                 

                this is the SQL query and when I paste that into Qlikview i get this error:

                SQL##f - SqlState: 42601, ErrorCode: 4294967192, ErrorMsg: [Microsoft][ODBC DB2 Driver]An unexpected token 00011 was found following ES.NOTICE_NBR IN (''. Expected tokens may include: ,. SQLSTATE: 42601, SQLCODE: -104

              • Re: match() clause in qlikview sql select script
                Sunny Talwar

                May be this

                 

                DeferredFees:

                LOAD Distinct

                     PROD_DT,

                     MSTR_ACCT_NBR,

                     Sum(DEFER_BAL_AMT) as Defer_fee_bal_amt

                Group By PROD_DT, MSTR_ACCT_NBR;

                SQL SELECT

                     DW.V_COMM_LOAN_FEES_MTH.PROD_DT,

                     DW.V_COMM_LOAN_FEES_MTH.MSTR_ACCT_NBR,

                     DW.V_COMM_LOAN_FEES_MTH.DEFER_BAL_AMT

                FROM

                     DW.V_COMM_LOAN_FEES_MTH

                WHERE

                          (DW.V_COMM_LOAN_FEES_MTH.PROD_DT = (SELECT MAX (DW.V_COMM_LOAN_FEES_MTH.PROD_DT)           FROM

                               DW.V_COMM_LOAN_FEES_MTH

                          WHERE

                               DW.V_COMM_LOAN_FEES_MTH.PROD_DT >=  {D '$(vMonthStart)'} and                DW.V_COMM_LOAN_FEES_MTH.PROD_DT <= {D '$(vMonthEnd)'})) and

                               DW.V_COMM_LOAN_FEES_MTH.NOTICE_NBR IN ('00011','00031','00043','00044','00045','00046'));