How to select records with multiple conditions

[#1]

I'm writing a WIP report using QuickBooks Advanced Reporting, which is built on QlikView.  I have built a pivot table that collects the data I need, but now I need to filter the records based on multiple conditions.  Specifically:

[Customer.Job Start Date] is not blank and

(([Customer.Job End Date] is blank) or ([Customer.Job End Date] > 10/31/2016)

Using a list box, I have been able to select records where [Customer.Job End Date] > 10/31/2016 quite easily using a list box and advanced search.  I have not been able to select records where either of these fields is blank or not blank, much less combine all these conditions.

I'm a newbie, so perhaps this is simple and I just don't know the proper syntax.  Can anybody suggest a method to accomplish this?

[swuehl]

Maybe like

=len(trim([Customer Job Start Date]))>0 and (len(trim([Customer Job End Date]))=0 or [Customer Job End Date] > makedate(2016,10,31) )

[#3]

Thanks Stefan.  I tested this in pieces.

[Customer Job End Date] > makedate(2016,10,31) does yield records with end dates greater than 10/31/2016

len(trim([Customer Job End Date]))=0 yields all records

len(trim([Customer Job Start Date]))>0 yields records with a start date

I set up a table with the expression len(trim([Customer.Job End Date])).  The expression yields two values, 0 and 10.  If I set the advanced search to len(trim([Customer.Job End Date]))=10, I get only records with a value in Customer.Job End Date.  However, if I set it to len(trim([Customer.Job End Date]))=0, I get all records.  If I set it to len(trim([Customer.Job End Date]))=9, I get all records.  If I set it to len(trim([Customer.Job End Date]))<10, I get all records.  If I set it to len(trim([Customer.Job End Date]))<>10, I get all records. If I set it to len(trim([Customer.Job End Date]))>0, I get only records with a value in Customer.Job End Date.  If I set it to not (len(trim([Customer.Job End Date]))=10), I get all records.

Any thoughts?

[swuehl]

Could you post some more details about your data model and which field you are trying to apply the advanced search to?

I am not familiar with Quickbooks AR, does this store a QVW file you could maybe share?

Or are you maybe able to create  small sample application using QlikView personal edition and share this sample?

[#5]

Apparently handling nulls in QlikView is a problem.  I found QlikView Addict: Handling Nulls in QlikView while searching, and it has a hint that helps, but hasn't provided the final answer.  See his section on selecting nulls.

This works well as far as it goes.  I created a list box for [Customer.Name] and in Advanced Search entered:

=IsNull([Customer.Job End Date])

This gives my a list of jobs for which the job end date is blank.

I can also enter into this advanced search:

=([Customer.Job End Date]) > MakeDate(2016,10,31)

which yields all jobs with an end date later than 10/31/2016.

Unfortunately, putting in both of these conditions connected with an "or" gives me only the results of the first condition.  Do you have a thought on how to make this happen?

I do have a .qvw file I could upload.  Will it help you if you don't have the data model?

[swuehl]

A QVW also holds the model, so it might indeed help.

[#7]

Here it is.  Let me know what you think.

[swuehl]

It seems there is section access applied to the QVW, i.e. it's asking for userid (and password?).

Could you remove the section access script part and reload your document?

[#9]

Stefan, I'm afraid that's beyond my skill set.  QuickBooks Advanced Reporting is bundled with QuickBooks.  It's probable that the user name and password are somehow part of the linkage with QB.  I know that any user with access to Advanced Reporting can open these reports, so it seems unlikely that it is just my user name and password.  I don't have a stand-alone copy of Qlik View, and wouldn't have the skills to modify the script even if I had the software.

As it stands, I can use the individual conditions to get the report I want in two pieces, then combine them in Excel.  It just seems a shame that I can't use the tool to get the whole report in one run.

Thanks for your help!

[#10]

You need not solve this in qlik surface. Just go into your load script and make a resident load like:

Table_New:

NoConcatenate

Load *, if(isnull([Customer.Job End Date]),1,0) as NoJobEndDate

resident Table_old;

drop table Table_old;

//here you have an extra fiel where you can slect whether there is a value in the field or not

//or if you prefere, you can use this:

Table_New:

NoConcatenate

Load *, if(isnull([Customer.Job End Date]),'No value',[Customer.Job End Date]) as NoJobEndDate

resident Table_old;

drop table Table_old;

//here you can slect the tetxt "No Value" inside your field to find the "empty" values

Hope that helps

Regards

Sabine