How to use where clause in getting current month and year?

[kristeljoymalapitan]

I need to get all acctno maturitydate previous month, but upon loading below script (tried query1 and query2) lines fetched is zero (0)

File.qvd  contains hundreds of acctno that has maturitydate in previous month.

..query1

Let vdateref = date(today()-1,'MM-YYYY');

tbl1:

acctno

maturitydate

from File.qvd

where maturitydate = $(vdateref );

..query2

tbl1:

acctno

maturitydate

from File.qvd

where date(maturitydate,'MM-YYYY') = date(today()-1,'MM-YYYY');

[jensmunnichs · Accepted solution]

1. date(today()-1,'MM-YYYY') will take today's date, subtract one day, and then format it as MM-YYYY. This is obviously not what you want. Instead, use Addmonths() to get to the previous month.

2. This might not actually be necessary, but it's possible Qlikview doesn't recognise 'maturitydate' as a date or number and/or that maturitydate is formatted differently than MM-YYYY. The Date() function takes a number as its first argument and formats it as the second argument (or the standard date format if no second argument is given). To ensure that the date is in the correct format for the where clause, you can use Date#(). This function is used to tell Qlikview that the first argument is a text string representing a date formatted as the second argument. You can then use the result of that as the first argument for the Date() function to get the format you want.

So the result would be something like this:

tbl1:

acctno,

maturitydate

from File.qvd

where Date(Date#(maturitydate, 'DD-MM-YYYY'), 'MM-YYYY') = Date(Addmonths(Today(), -1), 'MM-YYYY');

Some notes:

1. If you are planning on using the 'previous month date' more often, store it as a variable as you did in your first query.

2. The second argument has to be the exact format of maturitydate in your source data, including separators (for example DD/MM/YYYY), so change that accordingly.

Let me know if that works for you and/or if you have any questions!

[kristeljoymalapitan]

Hi Jensmunnichs,

I tried your above advice but still lines fetched is zero.

Please be informed that maturitydate is just a reference column, please see below query used to get maturitydate

temp_tbl:

select

acctno

Date(Addmonths(Today(),-1),'MM-YYYY') AS maturitydate

from DB_table;

Store temp_tbl into File.qvd;

[kristeljoymalapitan]

Hi Jensmunnichs,

I tried your above advice but still lines fetched is zero.

Please be informed that maturitydate is just a reference date column , please see below query used to get maturitydate column

temp_tbl:

Load

acctno

Date(Addmonths(Today(),-1),'MM-YYYY') AS maturitydate

from DB_table;

Store temp_tbl into File.qvd;

Resending since I  made correction on above statement ..

[miskinmaz · Accepted solution]

Even if you are formating the date in MM-YYYY it does not mean that it will represent the correct picture. In background the numeric value of date does not change.

Just to cross check what value your variable and maturity date is having try to use num() function on both variable and maturity date. Both values will differ.

One way to resolve your issue is try to use monthstart() function on both your variable and maturity date

[kristeljoymalapitan]

Thank you so much Miskinmaz and Jensmunnichs for accomodating my concern, It works! 🙂

[jensmunnichs]

You are absolutely correct, my bad!