topic Re: Selecting the NULL values in QlikView

Selecting the NULL values

Tue, 18 Dec 2012 13:10:47 GMT

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

Re: Selecting the NULL values

Gysbert_Wassenaar — Tue, 18 Dec 2012 13:54:43 GMT

See attached qvw. Is that what you need?

Re: Selecting the NULL values

Tue, 18 Dec 2012 13:57:22 GMT

Gysbert, can't see any attachment .. ?

Re: Selecting the NULL values

Tue, 18 Dec 2012 13:57:31 GMT

Gysbert, can't see any attachment .. ?

Re: Selecting the NULL values

Tue, 18 Dec 2012 13:59:52 GMT

Gysbert, perfect. Thanks a ton.

Re: Selecting the NULL values

Tue, 18 Dec 2012 14:31:42 GMT

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 ?

Re: Selecting the NULL values

Gysbert_Wassenaar — Tue, 18 Dec 2012 14:35:47 GMT

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.

Re: Selecting the NULL values

Tue, 18 Dec 2012 14:58:14 GMT

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 ?

Re: Selecting the NULL values

Gysbert_Wassenaar — Tue, 18 Dec 2012 15:06:46 GMT

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

Re: Selecting the NULL values

Tue, 18 Dec 2012 15:14:15 GMT

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)

Re: Selecting the NULL values

Gysbert_Wassenaar — Tue, 18 Dec 2012 15:30:03 GMT

Ah, I understand now. Ok, plan B. Let's try nullcount(). See attached example

Re: Selecting the NULL values

Tue, 18 Dec 2012 16:21:12 GMT

This one works perfectly .. thank you once again