How to Drop/Remove Values From Field?

[#1]

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

[danieloberbilli · Accepted solution]

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],

[qlikviewwizard]

Hi jasonliau,

Check you date format in all over the data model as same format like SET Date format = 'DD-MMM-YYYY'

[#4]

EDIT: Actually this is correct. I was missing the extra ) before as. Thank you!

[#5]

Hi,

Yes, confirmed that the date format is the same. The issue is when there are no date values for both fields.

[Anonymous]

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]

[#7]

Perfect, thanks!