topic Re: How to select records with multiple conditions in QlikView

How to select records with multiple conditions

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: How to select records with multiple conditions

swuehl — Wed, 09 Nov 2016 22:46:32 GMT

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) )

Re: How to select records with multiple conditions

Thu, 10 Nov 2016 15:09:00 GMT

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?

Re: How to select records with multiple conditions

swuehl — Thu, 10 Nov 2016 17:57:21 GMT

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?

Re: How to select records with multiple conditions

Thu, 10 Nov 2016 19:40:37 GMT

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?

Re: How to select records with multiple conditions

swuehl — Fri, 11 Nov 2016 15:25:22 GMT

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

Re: How to select records with multiple conditions

Fri, 11 Nov 2016 15:42:55 GMT

Here it is. Let me know what you think.

Re: How to select records with multiple conditions

swuehl — Fri, 11 Nov 2016 22:26:05 GMT

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?

Re: How to select records with multiple conditions

Mon, 14 Nov 2016 15:27:21 GMT

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!

Re: How to select records with multiple conditions

Thu, 19 Jan 2017 09:29:19 GMT

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