Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. $300 savings extended to February 9th Learn More
Not applicable

How to Drop/Remove Values From Field?

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

1 Solution

Accepted Solutions
danieloberbilli
Valued Contributor II

Re: How to Drop/Remove Values From Field?

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

View solution in original post

6 Replies
danieloberbilli
Valued Contributor II

Re: How to Drop/Remove Values From Field?

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

View solution in original post

qlikviewwizard
Honored Contributor II

Re: How to Drop/Remove Values From Field?

Hi jasonliau,

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

Not applicable

Re: How to Drop/Remove Values From Field?

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

Not applicable

Re: How to Drop/Remove Values From Field?

Hi,

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

mov
Esteemed Contributor III

Re: How to Drop/Remove Values From Field?

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]

Not applicable

Re: How to Drop/Remove Values From Field?

Perfect, thanks!