Selecting entries with not records in in another table

[#1]

Hi all,

This is i am hoping a simple issue but I am struggling with it.

I have two table accounts and assets.  linked by account ID.

What I want to do is select all the accounts that have no assets of have not brought any assets in the last five years.  (there is a date of purchase in the asset table)

I am hoping to do this via a selection button I cannot seem to get the selection correct.

Any suggestions very welcome.

many thanks

Paul

[Gysbert_Wassenaar]

This can't be done with just selections afaik since missing data can't be selected. What you can use instead are set analysis expressions. See here for a similar question: how to count null records or Calculating Clients Turnover or Point in time Reporting - New / Lost / Total Customer . Can you post a file with some sample data? That would make it a lot easier to come up with a solution to your problem.

[Josh_Good]

You could an expression to make this selection on the account field.  If you search on the accounts list box
=isNull(assets) or something similar then you will select all accounts where assets is null.

Here is a video that goes through doing this:

Using Expressions to Make Selections

[maxgro]

hope the doc can help you: search for NULLs

http://community.qlik.com/blogs/qlikviewdesignblog/2013/05/02/finding-null

[Anonymous]

You can select accounts with no assets by selecting all data in assets, and after that select excluded values in Account ID.

Can be implemented in a variety of ways, as pointed out already.

[#6 · Accepted solution]

I used the follow8ing in the end following several of your suggestions thanks.

='=NullCount(Last5YearsFlag)>0 Or (Last5YearsFlag)=0'

I had to add some flags but got there thanks for your help.