Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

jananireddy
Contributor

Date format in QVD where condition

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

1 Solution

Accepted Solutions

Re: Date format in QVD where condition

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

2 Replies

Re: Date format in QVD where condition

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
Contributor

Re: Date format in QVD where condition

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

Community Browser