Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

rory_mchugh
New Contributor III

Date syntax in if statement

Hi

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

Tags (1)
1 Reply
Not applicable

Re: Date syntax in if statement

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