topic Re: Counting blank values from left join in QlikView

Counting blank values from left join

zagzebski — Tue, 25 Jun 2013 20:16:39 GMT

I want to count fields that do not have a join back to the main table. For instance in the table below if I left joined to Table 2 from Table 1. How do I count the two effective dates (Product D and F) that had no corresponding join to the main table (Table 1). Is this possible?

Example

Product(Table 1) Zone(Table 1) Product (Table 2) Effective Date (Table2)

A North A 12/31/2012

B South B 1/31/2011

C East C 9/27/2010

D West - -

E North E 8/21/2012

F East - -

Re: Counting blank values from left join

Anonymous — Tue, 25 Jun 2013 20:32:39 GMT

Possible, with set analysis. I guess you count Product which doesn't have Effective Date.
Something like this:
count({<[Effective Date]-={*}>} Product)

Regards,
Michael

Re: Counting blank values from left join

zagzebski — Tue, 25 Jun 2013 20:36:58 GMT

Thanks for the response.

Unfortunately it just returns a 0. Any thoughts?

Re: Counting blank values from left join

Anonymous — Tue, 25 Jun 2013 20:46:43 GMT

OK, try this:

count({<[Effective Date]=> - <[Effective Date]={*}>} Product)

Re: Counting blank values from left join

zagzebski — Tue, 25 Jun 2013 20:49:51 GMT

Awesome- it worked!

Not exactly sure how it works though - do you have a brief description on this set analysis so I can use for future reference.

Re: Counting blank values from left join

Anonymous — Tue, 25 Jun 2013 20:52:13 GMT

No secrets, it is all in "help" 🙂

(Plus spending time on top of that...)

Re: Counting blank values from left join

zagzebski — Tue, 25 Jun 2013 20:53:26 GMT

Thanks - I hadn't seen it in help before I will try to track down.