Date format in QVD where condition

[jananireddy]

Hi Guys,

I am having trouble to get a QVD evaluate its where condition.The condition i am applying is to load records whose date is less than a certain date 

Below is the script used in the data load editor:

Load date(max(ApplicationDate),'DD/MM/YYYY') as vDate From [lib://QVD/Branch.QVD] (qvd) ;

Load * From [lib://QVD/LOANSHISTONETIMENEW.QVD] (qvd)

where date(DateBrn,'DD/MM/YYYY') = DATE(DATE#('vDate','DD/MM/YYYY'), 'DD/MM/YYYY') ;

I have read this very helpful link before posting this question, but i couldn't solve my issue

Dealing with date formats in QlikView and QlikSense

These are some options i have tried to format the date. Hard coding the date works just fine but nothing else.

//where date(DateBrn,'DD/MM/YYYY') < date#('vDate','DD/MM/YYYY');

//where date(DateBrn,'DD/MM/YYYY') < date#('27/12/2017','DD/MM/YYYY'); // this worked a couple of times but it did not work the next day

//where date(DateBrn,'DD/MM/YYYY') < '27/12/2017';

//where date(DateBrn,'DD/MM/YYYY') < date('$(vDate)','DD/MM/YYYY');

[marcus_sommer · Accepted solution]

You couldn't grab a field-value in this way (you need a variable for it) and the best way to calculate with dates or to compare them is just to take their numeric value. This could mean in your case something like this:

ToDate:

Load max(ApplicationDate) as vDate From [lib://QVD/Branch.QVD] (qvd);

let vDate = peek('vDate', -1, 'ToDate');

Load * From [lib://QVD/LOANSHISTONETIMENEW.QVD] (qvd)

where num(DateBrn) < $(vDate);

- Marcus

[jananireddy]

My problem is a lot clearer now, thanks for the timely help