topic Re: Date format in QVD where condition in App Development

Date format in QVD where condition

jananireddy — Fri, 29 Dec 2017 07:12:01 GMT

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');

Re: Date format in QVD where condition

marcus_sommer — Fri, 29 Dec 2017 07:23:00 GMT

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

Re: Date format in QVD where condition

jananireddy — Fri, 29 Dec 2017 12:21:20 GMT

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