topic Re: match() clause in qlikview sql select script in QlikView

match() clause in qlikview sql select script

Anonymous — Tue, 12 Dec 2017 15:13:23 GMT

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_Samineni — Tue, 12 Dec 2017 15:19:42 GMT

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 — Tue, 12 Dec 2017 15:19:43 GMT

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.

Re: match() clause in qlikview sql select script

Anonymous — Tue, 12 Dec 2017 15:33:01 GMT

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

Anonymous — Tue, 12 Dec 2017 15:53:35 GMT

I get this error when i try this:

SQL##f - SqlState: 42601, ErrorCode: 4294967192, ErrorMsg: [Microsoft][ODBC DB2 Driver]An unexpected token and was found following ', '00055', '00056'). Expected tokens may include: <comp_op> <quantifier> <subquery>. SQLSTATE: 42601, SQLCODE: -104

Re: match() clause in qlikview sql select script

sunny_talwar — Tue, 12 Dec 2017 15:58:51 GMT

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'));

Re: match() clause in qlikview sql select script

Anil_Babu_Samineni — Tue, 12 Dec 2017 16:34:25 GMT

Because, DB doesn't have Match() function. Instead you need to use In operator.

Re: match() clause in qlikview sql select script

evan_kurowski — Tue, 12 Dec 2017 16:56:32 GMT

Ashley Navin wrote:

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.

As Anil points out, unless there is parameterization with Qlik variables, native SQL should remain verbatim to the syntax that works outside of QlikView. It looks like there was a premature attempt to translate syntax.