topic Re: how to define where clause for date in QlikView

how to define where clause for date

qliklearnervir — Thu, 06 Jul 2017 04:28:14 GMT

Hi,

I want to filter my LOad process with date.

where createddate = 45678.7685 like that/...

vToday=Date(Today(),'YYYYMMDD');

Load Statment :

LOAD Booking,

Booking Number

FROM $(vQvdlaction)Booking.qvd(qvd)

where Date(createddate,'YYYYMMDD')= $(vToday());

but i am getting 0 outcome...

but in data there are 50 rows with createddate =today()...

PLEASE SUGGEST THE WAY TO CONVERT TIME IN WHERE CONDITION TO LOAD DESIRED OUTCOME.

Re: how to define where clause for date

prma7799 — Thu, 06 Jul 2017 04:48:29 GMT

Your script looks like good

Please try like below

where Date(createddate,'YYYYMMDD') = date(Today(),'YYYYMMDD');

Or post some sample data.

Re: how to define where clause for date

BalaBhaskar_Qlik — Thu, 06 Jul 2017 05:10:44 GMT

may be:

vToday=Date(floor(Today()),'YYYYMMDD');

vToday=Date(floor(Today()),'YYYY-MM-DD');

Statement:

LOAD Booking,

Booking Number

FROM $(vQvdlaction)Booking.qvd(qvd)

where Date(floor(createddate),'YYYYMMDD')= $(vToday());

//where Date(floor(createddate),'YYYY-MM-DD')= $(vToday());

Re: how to define where clause for date

Anil_Babu_Samineni — Thu, 06 Jul 2017 05:13:21 GMT

You may mis understand the logic over here

Date(45678.7685) Returns 01/21/2025

Date(Today()) Returns Today's date of your machine.

Is that your machine showing same date like 01/21/2025 for you? If so, This condition true nor false

Re: how to define where clause for date

kamal_sanguri — Thu, 06 Jul 2017 05:42:20 GMT

the same way you define for other values. You just have to make sure the format of values in field at the left side matches with the format at right side.

For Ex:

Due Date = 1-Jan-2017

Where

DueDate < '2-Jan-2017'

If they are in different format you can convert them using Date or Date# function.

Re: how to define where clause for date

kamal_sanguri — Thu, 06 Jul 2017 06:03:38 GMT

Try this way:

Just use today() instead of variable in Where clause.

Refer attached QV document.

Data:

LOAD createddate

Where newdate = Today();

LOAD createddate,

Date(round(createddate),'YYYYMMDD') as newdate;

LOAD *

Inline

[

createddate

42921.7825

45678.7685

45678.7686

];