Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have dates in either of two fields below and I use the below in my load script to turn it into Fiscal Quarter (Q1, Q2, Q3, Q4):
'Q' & ceil(month(if(isnull([Inquiry Capture Date]),[Date Time],[Inquiry Capture Date]) + 273)/3) as [Response Qtr], |
This works great but there are rows where it is null in both fields and creates just Q. How can I get rid of the Q in either the load script or in an expression? (Preferably in my load script)
Thanks in advance!!
-Jason
best to use len(trim(FIELD))>0
e.g.
if(len(trim([Date Time]))=0 and len(trim([Inquiry Capture Date]))=0, Null(),
'Q' & ceil(month(if(isnull([Inquiry Capture Date]),[Date Time],[Inquiry Capture Date]) + 273)/3)) as [Response Qtr],
best to use len(trim(FIELD))>0
e.g.
if(len(trim([Date Time]))=0 and len(trim([Inquiry Capture Date]))=0, Null(),
'Q' & ceil(month(if(isnull([Inquiry Capture Date]),[Date Time],[Inquiry Capture Date]) + 273)/3)) as [Response Qtr],
Hi jasonliau,
Check you date format in all over the data model as same format like SET Date format = 'DD-MMM-YYYY'
EDIT: Actually this is correct. I was missing the extra ) before as. Thank you!
Hi,
Yes, confirmed that the date format is the same. The issue is when there are no date values for both fields.
if( (len(trim([Inquiry Capture Date])) + len(trim([Date Time])))>0,
'Q' & ceil(month(if(isnull([Inquiry Capture Date]),[Date Time],[Inquiry Capture Date]) + 273)/3)
) as [Response Qtr]
Perfect, thanks!