topic Re: Where clause in QlikView

Where clause

dmxmikey — Wed, 25 Nov 2020 16:16:04 GMT

I have below script and need to filter data where the field act-date is <= to today and was wondering how to do that.

Conprice:

SQL SELECT "act-date",

FROM price;

Re: Where clause

neelamsaroha157 — Fri, 09 Feb 2018 15:26:28 GMT

Try this:

SQL SELECT "act-date"

FROM price

where [act-date] <= CURRENT_TIMESTAMP

;

Re: Where clause

dmxmikey — Fri, 09 Feb 2018 15:39:05 GMT

I have tried the below but get this error

Syntax error in SQL statement at or about "[act-date] <= num(Today())" (10713)

SQL SELECT "act-date"

FROM price

where [act-date] <= num(Today());

Re: Where clause

lcontezini — Fri, 09 Feb 2018 15:45:47 GMT

The date format for "today()" must be the same as [act-date]. I'd suggest you import [act-date] with no filter in order to check the format.

Then you can use it in the where clause with the format you want, maybe like date(today(), 'YYYYMMDD'), or something like this.

Re: Where clause

neelamsaroha157 — Fri, 09 Feb 2018 15:47:08 GMT

instead of num(Today()), use CURRENT_TIMESTAMP if your date format is in timestamp or use GETDATE() if you format is Date.

As you are using direct SQL query instead of Load statement you need to use the SQL date function

Re: Where clause

dmxmikey — Fri, 09 Feb 2018 16:02:02 GMT

as below?

where [act-date]<= date(today(), 'DDMMYYYY');

Re: Where clause

lcontezini — Fri, 09 Feb 2018 16:14:34 GMT

Yes, but first you need to check the format for [act-date]. It can be anything like 'DDMMYYYY', or 'YYYYMMDD'... Follow the steps:

1. Load this:

Conprice:

SQL SELECT "act-date",

FROM price;

2. Take a look at [act-date] format.

It can be anything like DDMMYYYY, YYYYMMDD, YYYY.MM.DD ...

3. Insert the format in the date clause, replacing the 'YYYYMMDD' bellow:

Where [act-date]<= date(today(), 'YYYYMMDD');

Re: Where clause

dmxmikey — Fri, 09 Feb 2018 16:35:30 GMT

here is my full script and still error.

Conprice:

SQL SELECT "act-date",

"c-type",

"claim-back",

"con-group",

"con-type",

cost,

"cost-rbon",

"cost-uplift",

"cust-code",

"deact-date",

depot,

discount,

"input-date",

"last-sold",

"last-update",

"list-price",

lwsv,

"net-cost",

"o-ordqty",

operator,

"p-code",

qty,

"qty-in",

reported,

"sell-price",

"tran-stat-e",

"tran-stat-s",

"upd-date",

weight,

wsv

FROM PUB.conprice

Where [act-date]<= date(today(), 'DDMMYYYY');

Re: Where clause

dmxmikey — Fri, 09 Feb 2018 16:35:47 GMT

The date is DDMMYYYY

Re: Where clause

lcontezini — Fri, 09 Feb 2018 16:43:09 GMT

LET vMax_Date = '"'&date(today(),'DDMMYYYY')&'"';

Conprice:

SQL SELECT "act-date",

"c-type",

"claim-back",

"con-group",

"con-type",

cost,

"cost-rbon",

"cost-uplift",

"cust-code",

"deact-date",

depot,

discount,

"input-date",

"last-sold",

"last-update",

"list-price",

lwsv,

"net-cost",

"o-ordqty",

operator,

"p-code",

qty,

"qty-in",

reported,

"sell-price",

"tran-stat-e",

"tran-stat-s",

"upd-date",

weight,

wsv

FROM PUB.conprice

Where [act-date] <= $(vMax_Date);

Re: Where clause

dmxmikey — Fri, 09 Feb 2018 16:47:11 GMT

SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "[act-date] <= "09022018"" (10713)

Conprice:

SQL SELECT "act-date",

"c-type",

"claim-back",

"con-group",

"con-type",

cost,

"cost-rbon",

"cost-uplift",

"cust-code",

"deact-date",

depot,

discount,

"input-date",

"last-sold",

"last-update",

"list-price",

lwsv,

"net-cost",

"o-ordqty",

operator,

"p-code",

qty,

"qty-in",

reported,

"sell-price",

"tran-stat-e",

"tran-stat-s",

"upd-date",

weight,

wsv

FROM PUB.conprice

Where [act-date] <= "09022018"

Re: Where clause

neelamsaroha157 — Fri, 09 Feb 2018 17:05:39 GMT

Can you share how the actual values in the field act-date looks like.

Load only this field without any where clause.

Re: Where clause

dmxmikey — Fri, 09 Feb 2018 17:18:42 GMT

Re: Where clause

lcontezini — Fri, 09 Feb 2018 17:22:29 GMT

Your date format is not that you said before. Try this:

LET vMax_Date = '"'&date(today(),'DD/MM/YYYY')&'"';

Conprice:

SQL SELECT "act-date",

"c-type",

"claim-back",

"con-group",

"con-type",

cost,

"cost-rbon",

"cost-uplift",

"cust-code",

"deact-date",

depot,

discount,

"input-date",

"last-sold",

"last-update",

"list-price",

lwsv,

"net-cost",

"o-ordqty",

operator,

"p-code",

qty,

"qty-in",

reported,

"sell-price",

"tran-stat-e",

"tran-stat-s",

"upd-date",

weight,

wsv

FROM PUB.conprice

Where [act-date] <= $(vMax_Date);

Re: Where clause

alis2063 — Fri, 09 Feb 2018 17:23:42 GMT

Conprice:

SQL SELECT "act-date",

FROM price

WHERE CAST([act-date] AS DATE)=GETDATE();

tRY IT.

Re: Where clause

dmxmikey — Fri, 09 Feb 2018 17:31:30 GMT

Conprice:

SQL SELECT "act-date",

"c-type",

"claim-back",

"con-group",

"con-type",

cost,

"cost-rbon",

"cost-uplift",

"cust-code",

"deact-date",

depot,

discount,

"input-date",

"last-sold",

"last-update",

"list-price",

lwsv,

"net-cost",

"o-ordqty",

operator,

"p-code",

qty,

"qty-in",

reported,

"sell-price",

"tran-stat-e",

"tran-stat-s",

"upd-date",

weight,

wsv

FROM PUB.conprice

Where [act-date] <= "09/02/2018"

Re: Where clause

neelamsaroha157 — Fri, 09 Feb 2018 17:33:42 GMT

SQL SELECT "act-date"

FROM PUB.conprice

Where CONVERT(Date, act-date) <= '2017-09-25'

;

for Today's date, Use -

SQL SELECT "act-date"

FROM PUB.conprice

Where CONVERT(Date, act-date) <= GETDATE()

;

Re: Where clause

dmxmikey — Fri, 09 Feb 2018 17:40:12 GMT

SQL##f - SqlState: S1000, ErrorCode: 4294757240, ErrorMsg: [DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Syntax error in SQL statement at or about "Date, act-date) <= GETDATE() " (10713)

Conprice:

SQL SELECT "act-date",

"c-type",

"claim-back",

"con-group",

"con-type",

cost,

"cost-rbon",

"cost-uplift",

"cust-code",

"deact-date",

depot,

discount,

"input-date",

"last-sold",

"last-update",

"list-price",

lwsv,

"net-cost",

"o-ordqty",

operator,

"p-code",

qty,

"qty-in",

reported,

"sell-price",

"tran-stat-e",

"tran-stat-s",

"upd-date",

weight,

wsv

FROM PUB.conprice

Where CONVERT(Date, act-date) <= GETDATE()

Re: Where clause

dmxmikey — Fri, 09 Feb 2018 17:42:23 GMT

Re: Where clause

alis2063 — Fri, 09 Feb 2018 17:56:51 GMT

Hi Mike,

It will sure help

Conprice:

SQL SELECT "act-date",

FROM price

WHERE CAST([act-date] AS DATE)=cast(GETDATE()as date);