Date syntax in if statement

rory_mchugh — Thu, 09 May 2013 12:51:17 GMT

I am trying to write a simple if statement to group my dates by financial year. (I am dealing with just 14 dates, so happy to hard code this, rather than use a more complicated script to get a financial year for any date.)

This is the script:

MonthlyReportDatesTemp:

Load

Distinct UploadEngagementDate as UploadEngagementDate

Resident UploadEngagementCLRN;

MonthlyReportDates:

Load

UploadEngagementDate,

Month(UploadEngagementDate) as UploadEngagementMonth,

if(((UploadEngagementDate >= 01/04/2012) and (UploadEngagementDate < 01/04/2013)),'1213','1314') as UploadEngagementFY

Resident MonthlyReportDatesTemp

Order By UploadEngagementDate asc;

DROP Table MonthlyReportDatesTemp;

This is the table that the script is pointed at.

LeadCLRN	UploadEngagementDate	UploadEngagement%CLRN
BBC	16/04/2012	0.92895586652314
BBC	21/05/2012	0.99891540130152
BBC	18/06/2012	0.98692810457516
BBC	16/07/2012	0.97986577181208
BBC	20/08/2012	0.99325084364454
BBC	17/09/2012	0.9875
BBC	15/10/2012	0.98470588235294
BBC	19/11/2012	0.97690531177829
BBC	17/12/2012	0.9749430523918
BBC	21/01/2013	0.97477064220183
BBC	18/02/2013	0.97119815668203
BBC	18/03/2013	0.96579247434436
BBC	15/04/2013	0.95537757437071
BBC	07/05/2013	0.97368421052632
CDTV	16/04/2012	0.98888888888889
CDTV	21/05/2012	1
CDTV	18/06/2012	0.98958333333333
CDTV	16/07/2012	1
CDTV	20/08/2012	0.99029126213592

And this is the resulting table

UploadEngagementDate	UploadEngagementFY	UploadEngagementMonth
16/04/2012	1314	Apr
21/05/2012	1314	May
18/06/2012	1314	Jun
16/07/2012	1314	Jul
20/08/2012	1314	Aug
17/09/2012	1314	Sep
15/10/2012	1314	Oct
19/11/2012	1314	Nov
17/12/2012	1314	Dec
21/01/2013	1314	Jan
18/02/2013	1314	Feb
18/03/2013	1314	Mar
15/04/2013	1314	Apr
07/05/2013	1314	May

Am I using the wrong syntax for dates here or am I doing something else wrong? I have tried putting qualifiers round the date and using the real date format (e.g. 45786) but still not getting it right.

Any advice much appreciated.

Thanks
Rory

Re: Date syntax in if statement

Thu, 09 May 2013 15:31:23 GMT

did you try speech marks around dates, i.e. '01-04-2012' instead of just 01-04-2012?

topic Re: Date syntax in if statement in QlikView

Date syntax in if statement

Re: Date syntax in if statement