How to filter straight table

[Anonymous]

Hi,

I am sure this should be easy to do, but I can't seem to figure out the syntax needed.

My raw data table has a date opened, and date closed columns. There will always be a date opened, but if the word order is not completed, the date closed will be blank. I have been able to use the nullcount function to count how many open work orders there are.

But I created a table and I simple want to only display records in the table that have a null "date closed" entry.

1) Can this be done without having the Date Closed dimension in my table (Since I only want to show items that have no date closed, it does not make sense to include it as a column which will never have any data)

2) If not, what is the expression I should be using? I assume this needs to go in the formula box of the (Date Closed) Dimension within my table?

I have spent about 60 minutes searching posts and working with the 600+ page PDF on scripting but nothing has worked, so any help is greatly appreciated. I am pretty tech savvy but also unfamiliar with most of the Qlik jargon which makes learning from others much harder.

Thanks

[Oleg_Troyansky]

Hi Dustin,

this is a tricky one... The problem is that NULL values cannot be selected directly, not in a Filter Pane, and not using Set Analysis. All you can do is select another field (Work Order, for example) that is associated with a NULL value in the field {Date Closed]. For example, like this:

Count({<WorkOrder={"=isnull([Date Closed])"}>}  WorkOrder)

something like this.

cheers,

Oleg Troyansky

Check out my book QlikView Your Business - The Expert Guide for QlikView and Qlik Sense

[Anonymous]

Thanks for your reply.

Can this be done without the count function? This is not a pivot table. I am simply trying to create a standard table that looks identical to the excel file I upload for the source data, with the one exception being I only want to see records that have a null date closed.

The below screenshot is the excel file that gets uploaded, the QlikSense table I want to create will look identical however it would have all records with a date closed omitted.

[Oleg_Troyansky]

The count gives you the ability to filter your Work Orders based on the NULL criteria and to disable showing zeros. Without the count() function, you'd have to use IF() statements in your Dimensions that are a lot uglier and don't perform very well.

Instead of the count() you could use Set Analysis with the Date Opened field - use it in a Measure like this:

min( {<the same NULL condition>} [Date Opened])

Since you only have one Date Opened per Order, the min will return the same value, and the Set Analysis will perform the filtering.

[Anonymous]

You'd be best off creating a field in your load script.  Something like.

if(isnull([Date Closed]),'Closed','Open') as Status

Then your set analysis and filtering and everything else becomes much simpler.

[Anonymous]

Hi Wallo,

I am a novice user so I am clueless how to do this as you specified. But for simplicity sake, lets say I add an additional column in excel, so my new file has an additional column called "Work order status" and every record is either "Open" or "closed".

What is the syntax to do a set analysis for a specific value (Assuming the value is not Null)? It seems like it should be simple but I continue to consume time trying to make this work on my own.

[Anonymous]

You would need to use it in all your expressions (not dimensions) in the table.

For instance, let's say in your table you wanted to show how many days the work order your expression is open.

Your script could be if(isnull([Date Closed]),'Open','Closed') as Status

Or you could just add it to your spreadsheet like you mentioned.

So then let's say you want to see the number of days each order has been open.  Then you would have an expression in your table like.

=sum({<Status={'Open'}>}Today()-[Date Opened])

This would filter your table down to only open work orders.  The open work orders being the ones without a Date Closed.

And it gives you more options.  So you could also have a filter for Status that the user can click on.

[zebhashmi]

i believe there should be  many ways to do that. what about a residence load of the table with not exist

[Anonymous]

Thanks again for your help, I think I am not being clear in what I am asking.

I do not need to have a button at the top to filter for "Open". I want to hard code this into a chart table so that the data displayed only represents records that are open.

How can I do this? I don't under the syntax at all, what do I need to do differently?

[Anonymous]

I bet there are a bunch of ways to do this, I just need one to work though! Thanks for your reply, but I don't think that is what I am trying to do.

How can I filter a chart table by for a specific value within a field?

[Work Order Status] = 'Open" is showing up as an OK formula, but it keeps telling me the calculation condition is not fulfilled.