topic Re: Load Where. in QlikView

Load Where.

bnelson111 — Fri, 19 Jun 2015 07:19:24 GMT

Currently I have a working Load where Statement as follows.

Where "SysDate" > '01/01/2015';

How do i add the following where it filter down unnecessary data.

Receipt Info.TxnType equal to 2 or 3 or 6

i have tried

Where "SysDate" > '01/01/2015' and Where [Receipt Info.TxnType] = 2,3,6;

Thanks Brett, Maybe i have had a brain freeze

Re: Load Where.

kaushiknsolanki — Fri, 19 Jun 2015 07:23:13 GMT

Try,

Where "SysDate" > '01/01/2015' and match([Receipt Info.TxnType],2,3,6)

Regards,

Kaushik Solanki

Re: Load Where.

jagan — Fri, 19 Jun 2015 07:23:40 GMT

Hi,

Try this

Where "SysDate" > '01/01/2015' and Match( [Receipt Info.TxnType], 2,3,6);

Regards,

Jagan.

Re: Load Where.

Peter_Cammaert — Fri, 19 Jun 2015 07:24:26 GMT

If this is a WHERE clause in a LOAD statement, try with

WHERE (SysDate > '01/01/2015') AND Match(TxnType, 2, 3, 6);

If this is a SQL WHERE clause (e.g. used in a SELECT statement), please tell us what DBMS you're writting code for.

Peter

Re: Load Where.

Kushal_Chawda — Fri, 19 Jun 2015 07:25:40 GMT

Use the where as below

where date(SysDate,'DD/MM/YYYY')>date#('01/01/2015','DD/MM/YYYY')

and match([Receipt Info.TxnType] ,'2','3','6')

Re: Load Where.

bnelson111 — Fri, 19 Jun 2015 07:34:11 GMT

Getting the following error

SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "[Receipt Info.TxnType],2,3,6)" (10713)

SQL SELECT *

FROM PUB."PV_InvHistory"

Where "SysDate" > '01/01/2015' and match([Receipt Info.TxnType],2,3,6)

Re: Load Where.

bnelson111 — Fri, 19 Jun 2015 07:34:47 GMT

Getting the following error

SQL SELECT *

FROM PUB."PV_InvHistory"

Where "SysDate" > '01/01/2015' and match([Receipt Info.TxnType],2,3,6)

Re: Load Where.

bnelson111 — Fri, 19 Jun 2015 07:35:21 GMT

Getting back the following error

SQL SELECT *

FROM PUB."PV_InvHistory"

Where "SysDate" > '01/01/2015' and match([Receipt Info.TxnType],2,3,6)

Re: Load Where.

jagan — Fri, 19 Jun 2015 07:37:35 GMT

Hi,

If you are using in Database the use IN()

SQL SELECT *

FROM PUB."PV_InvHistory"

Where "SysDate" > '01/01/2015' and [Receipt Info.TxnType] IN (2,3,6);

Regards,

jagan.

Re: Load Where.

Peter_Cammaert — Fri, 19 Jun 2015 07:38:42 GMT

That is because the syntax we all suggested is for a LOAD statement, not for a SQL SELECT. You cannot mix parts of different script languages.

The WHERE clause should be put in Progress SQL, and that version may not have a match function. Does it have something comparable, or does it have the ...TxnType IN (2,3,6) set membership operator?

Peter

Re: Load Where.

Fri, 19 Jun 2015 07:39:49 GMT

Hi Brett,

LOAD *

FROM ABC

Where "SysDate" > '01/01/2015' and match([Receipt Info.TxnType],2,3,6);

Hope this helps.

Regards,

Snehal

Re: Load Where.

Peter_Cammaert — Fri, 19 Jun 2015 07:42:59 GMT

Postgress documentation says that it does support an IN operator. The above syntax is (probably) right.

Peter

Re: Load Where.

bnelson111 — Fri, 19 Jun 2015 12:08:16 GMT

Tried suggested, still getting the following

Getting back the following error

SQL SELECT *

FROM PUB."PV_InvHistory"

Re: Load Where.

Peter_Cammaert — Fri, 19 Jun 2015 12:15:50 GMT

You're still trying to stuff the match() function call in a SELECT statement. That SELECT statement will be sent as-is to your Postgress database which replies that it doesn't understand QlikView functions (match() is a QlikView function, not a SQL or DBMS function). Which is correct.

What happens if you try

SQL SELECT *

FROM PUB."PV_InvHistory"

WHERE ("SysDate" > '01/01/2015') AND ("Receipt Info.TxnType" IN (2,3,6));

Peter

Re: Load Where.

MayilVahanan — Fri, 19 Jun 2015 12:28:52 GMT

Try like this

Load * where SysDate > '01/01/2015' and Match( [Receipt Info.TxnType], 2,3,6);

SQL SELECT *

FROM PUB."PV_InvHistory"

Re: Load Where.

bnelson111 — Fri, 19 Jun 2015 12:31:42 GMT

Getting the following.

SQL##f - SqlState: S0022, ErrorCode: 4294757222, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Column "Receipt Info.TxnType" cannot be found or is not specified for query. (13865)

SQL SELECT *

FROM PUB."PV_InvHistory"

WHERE ("SysDate" > '01/01/2015') AND ("Receipt Info.TxnType" IN (2,3,6))

Re: Load Where.

bnelson111 — Fri, 19 Jun 2015 12:35:24 GMT

Everyone answer worked, Im so sorry i found a spelling problem. Sorry thanks everyone..

Re: Load Where.

MayilVahanan — Fri, 19 Jun 2015 12:37:03 GMT

Re: Load Where.

jagan — Sun, 21 Jun 2015 06:15:17 GMT

Hi,

Filtering the data in the SQL itself is the best method, because you don't need to bring all the data into qlikview and then on top filtering it in Qlikview is not a good practice. Usually it will take more time to load if data is huge. So best method is

Data:

SQL SELECT *

FROM PUB."PV_InvHistory"

Where "SysDate" > '01/01/2015' and [Receipt Info.TxnType] IN (2,3,6);

Hope this helps you.

Regards,

Jagan.