Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

dcarr0ll
New Contributor

How to filter straight table

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?

2017-05-25 11_37_42-Qlik Sense Desktop.png

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

Tags (1)
1 Solution

Accepted Solutions
atkinsow
Valued Contributor II

Re: How to filter straight table

You would use set analysis in an Measure/expression.

If you don't want any extra columns you can use what Oleg mentioned.  You would still need a Measure.

Then it could be

min( {<Status={'Open'}>} [Date Opened])


That's the exact syntax you would need

19 Replies
MVP
MVP

Re: How to filter straight table

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

dcarr0ll
New Contributor

Re: How to filter straight table

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.

2017-05-25 12_14_55-Bruce Bochy Was Steamed About A Game-Ending Strike Call.png

MVP
MVP

Re: How to filter straight table

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.

atkinsow
Valued Contributor II

Re: How to filter straight table

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.

dcarr0ll
New Contributor

Re: How to filter straight table

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.

atkinsow
Valued Contributor II

Re: How to filter straight table

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
Valued Contributor

Re: How to filter straight table

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

dcarr0ll
New Contributor

Re: How to filter straight table

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?

2017-05-25 12_14_55-Bruce Bochy Was Steamed About A Game-Ending Strike Call.png

dcarr0ll
New Contributor

Re: How to filter straight table

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.