Invalid expression help

[snancyiv]

I keep getting the invalid expression error when I try to load. I feel like I tried everything like removing single quotes, date function, num function, etc. Can anyone help solve what I'm doing wrong?

IF(num(month(date([DischargeDate])))=1,monthstart(date([DischargeDate]))) AS Min_DC_Date

[marcus_sommer]

Are you sure the error comes from this statement? Is there any aggregation included in the load?

[QFabian]

Hi @snancyiv , you can try adding # to dates, but you have to make sure your field is a valid date

IF(num(month(date#([DischargeDate])))=1,monthstart(date#([DischargeDate]))) AS Min_DC_Date

[snancyiv]

Yes, the error specifically highlights it. And no other aggregation. If this helps, I’m loading directly from a flat file and this expression is being added as another field in that load statement. 

[snancyiv]

It’s a valid date. I have another expression in that same load statement for the max and it works (which is pulling the most recent date of the flat file I’m loading because I need it for YTD calcs later). For this expression I need to pull the start of the fiscal year which is July 1 depending on what the discharge date is for each record. 

[QFabian]

are you using group by after the from statement? in that case, every expression has to be in an aggregation, example :

only(IF(num(month(date([DischargeDate])))=1,monthstart(date([DischargeDate])))) AS Min_DC_Date

[snancyiv]

No Group By. Here's my load statement. Are you saying I need to add group by?

[MAX_DATE]:
LOAD
MONTHEND(ADDMONTHS(MAX(DATE([DischargeDate],'DD/MM/YYYY')),-1)) as Max_DC_Date, 
IF(num(month(date([DischargeDate])))=1,monthstart(date([DischargeDate]))) AS Min_DC_Date //FISCAL START DATE OF MAX_DC_DATE
FROM [lib://QLIK Data Input/Quality/Vizient/Readms_PSH_ALL_RECORDS]
(txt, codepage is 28591, embedded labels, delimiter is '|', msq, header is 1 lines)