topic Re: Where condition Not working with QVD Load in QlikView

Where condition Not working with QVD Load

Thu, 25 Jun 2015 20:22:41 GMT

Facing issues,while using where condition in QVD load,

LET vDate = MakeDate(Year(Today()),Month(Today()),1);

Load * z,y,z,EndDate

from FROM [..\..\Shared QVD\test.qvd] (qvd)

where EndDate >= $(vDate)

Re: Where condition Not working with QVD Load

sunny_talwar — Thu, 25 Jun 2015 20:24:49 GMT

Why don't you try this:

Load * z,y,z,EndDate

FROM [..\..\Shared QVD\test.qvd] (qvd)

where EndDate >= Date(Today());

Re: Where condition Not working with QVD Load

sunny_talwar — Thu, 25 Jun 2015 20:25:44 GMT

Or this if you want from the starting of the month:

Load * z,y,z,EndDate

FROM [..\..\Shared QVD\test.qvd] (qvd)

Where EndDate >= Date(MonthStart(Today()));

Re: Where condition Not working with QVD Load

Anonymous — Thu, 25 Jun 2015 20:27:27 GMT

load without where condition and then load from resident table using where

TEST:

Load ..

from ..qvd

TEST1:

noconcatenate load *

resident TEST

where Endate>=$(vDate)

drop table TEST

loading from a qvd without where clause will be done by an optimized load which is much faster

(upto 10times faster)

Re: Where condition Not working with QVD Load

Thu, 25 Jun 2015 20:31:42 GMT

Unfortunately its not working....Is there any issue with the date format of End Date?

Re: Where condition Not working with QVD Load

sunny_talwar — Thu, 25 Jun 2015 20:34:01 GMT

Yes how does your EndDate Look? Can you share a screenshot of your EndDate data?

Re: Where condition Not working with QVD Load

Thu, 25 Jun 2015 20:37:26 GMT

Re: Where condition Not working with QVD Load

sunny_talwar — Thu, 25 Jun 2015 20:41:08 GMT

Try this:

Load * z,y,z,EndDate

FROM [..\..\Shared QVD\test.qvd] (qvd)

where Date(Date#(EndDate, 'MM/DD/YYYY')) >= Date(Today());

Re: Where condition Not working with QVD Load

petter — Thu, 25 Jun 2015 20:51:05 GMT

Often comparing dates as formatted dates and not as numerical dates can be quite hard to get working correctly.

Try to convert the dates to numerical dates:

LET vDate = MonthStart( Today() );

LET vNumDate = Num( vDate );

LOAD

EndDate

FROM [..\..\Shared QVD\test.qvd] (qvd)

WHERE Num(EndDate) >= $(vNumDate);

Re: Where condition Not working with QVD Load

Anonymous — Thu, 25 Jun 2015 20:54:06 GMT

Try

where EndDate >= '$(vDate)'

Re: Where condition Not working with QVD Load

Thu, 25 Jun 2015 20:54:41 GMT

I tried that,now I dont get any value for EndDate as well as X...wer am I worng

Re: Where condition Not working with QVD Load

maxgro — Thu, 25 Jun 2015 21:00:53 GMT

where EndDate >= '$(vDate)';

Re: Where condition Not working with QVD Load

Thu, 25 Jun 2015 21:37:16 GMT

Looks like the number conversion worked for me...Thank You everyone!!!

Re: Where condition Not working with QVD Load

jagan — Fri, 26 Jun 2015 07:04:53 GMT

HI,

For Dates also it will work but you have enclose variable in single quotes like below.

Try like this

LET vDate = MakeDate(Year(Today()),Month(Today()),1);

Load * z,y,z,EndDate

from FROM [..\..\Shared QVD\test.qvd] (qvd)

where Date(Floor(EndDate)) >= '$(vDate)';

OR Instead of using MakeDate() you can directly use Today() like below.

LET vDate = MakeDate(Year(Today()),Month(Today()),1);

Load * z,y,z,EndDate

from FROM [..\..\Shared QVD\test.qvd] (qvd)

where Date(Floor(EndDate)) >= Today();

Regards,

Jagan.