Selecting the NULL values

[#1]

Hi all,

This is what I want to do.

Table1 Structure=> AccountName,TotalPosition -- this is loaded from a flat file in a QVD

Table2 Structure=>AccountName,BusinessName -- this is loaded from a flat fiel in a QVD

Not all AccountName in Table1 are present in Table2, that is the relation between Table1 and table2 is 1->0,1

In one report, I show BusinessName,AccountName,TotalPosition -- suppressing any missing business name. Quite OK.

In another report, I need to show all AccountNames which do not belong to a BusinessName. That is, in an SQL sort of way:

select AccountName,TotalPosition from Table1 a where not exists (select 1 from Table2 b where a.AccountName=b.AccountName)

Any idea how to do it ?

Message was edited by: tapodevc -- adding the example QVW

[Gysbert_Wassenaar · Accepted solution]

See attached qvw. Is that what you need?

[#3]

Gysbert, can't see any attachment .. ?

[#4]

Gysbert, can't see any attachment .. ?

[#5]

Gysbert, perfect. Thanks a ton.

[#6]

Hmm .. this works to some extent. When clicked on the AccountName to filter further, the chart straight table loses the selectivity and shows up everything. See the attachment. Is there a way to stop that ?

[Gysbert_Wassenaar]

If you're using QV11 then you can add an alternate state and assign that state to the straight table. That way it will ignore any selections from other states.

[#8]

Alternate state doesn't help (I am using QV11). See attached. I guess when the user clicks on a particular 'AccountName' value in the 'Qty' chart, the {1-<BusinessName={*}> is evaluated as everything. You can add states to my previous attachement and see for yourself.

In reality, the list of Accounts not having a mapped Business will be long enough so that the user will want to click on a particular account (or select from a drop down on teh column name) to examine the Qty.

Any other idea pl ?

[Gysbert_Wassenaar]

I did add an alternate state to your previous attachment and it works perfectly here.

[#10]

Sorry Gysbert but that's not what I meant. If you click on the AccountName column in the row where account name is 'b' in the 'Qty' chart at the bottom of the screen, the table expands and shows all accounts though the selection for AS1 state is 'Account b'. See the attachment. As I said, in reality, there will be many rows in the bottom chart called 'Qty' and user will want to click or select one particular unmapped account and examine it's qty (and other attributes)