topic How to Drop/Remove Values From Field? in QlikView

How to Drop/Remove Values From Field?

Thu, 14 May 2015 03:17:15 GMT

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

Re: How to Drop/Remove Values From Field?

danieloberbilli — Thu, 14 May 2015 03:23:37 GMT

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

Re: How to Drop/Remove Values From Field?

qlikviewwizard — Thu, 14 May 2015 08:13:06 GMT

Hi jasonliau,

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

Re: How to Drop/Remove Values From Field?

Thu, 14 May 2015 16:52:47 GMT

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

Re: How to Drop/Remove Values From Field?

Thu, 14 May 2015 16:53:58 GMT

Hi,

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

Re: How to Drop/Remove Values From Field?

Anonymous — Thu, 14 May 2015 17:19:19 GMT

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]

Re: How to Drop/Remove Values From Field?

Thu, 14 May 2015 18:02:42 GMT

Perfect, thanks!