topic Re: Joining tables and counting new null values in QlikView

Joining tables and counting new null values

Tue, 17 Jan 2017 18:54:36 GMT

Hi everyone,

I'm joining two tables together by [ITMSNumber]. There's 11,000 [ITMSNumber]. Associated with that [ITMSNumber] there is [Application Status]. There is only 10,000 associations between [Application Status] and [ITMSNumber]. The rest of the 1,000 [ITMSNumber] get Null values for their [Application Status]. How do I count those Null values?

Thanks oodles!

Re: Joining tables and counting new null values

Digvijay_Singh — Tue, 17 Jan 2017 19:02:48 GMT

Try using the NullCount() function

Re: Joining tables and counting new null values

swuehl — Tue, 17 Jan 2017 19:08:31 GMT

Or maybe something like

Count( {1-<[Application Status] = {"*"}>} DISTINCT ITMSNumber)

Re: Joining tables and counting new null values

Tue, 17 Jan 2017 19:55:25 GMT

It seemed right but it didn't work. It just counted everything

Re: Joining tables and counting new null values

Tue, 17 Jan 2017 19:56:38 GMT

I did this and it returned a count of 1-

=NullCount ( {1-<[Application Status]>}DISTINCT ITMSNumber)

Re: Joining tables and counting new null values

swuehl — Tue, 17 Jan 2017 19:58:00 GMT

What about

Count( {<ITMSNumber = e({<[Application Status] = {"*"}>}) >} DISTINCT ITMSNumber)

Re: Joining tables and counting new null values

Tue, 17 Jan 2017 20:04:43 GMT

Thank you so much!

Re: Joining tables and counting new null values

Tue, 17 Jan 2017 20:13:47 GMT

Now how do I make an action that selects only those rows?

Re: Joining tables and counting new null values

swuehl — Tue, 17 Jan 2017 20:17:52 GMT

Use ITMSNumber as field in your Select in field action and as search string

='=Sum({<ITMSNumber = e({<[Application Status] = {"*"}>}) >} 1)'

Re: Joining tables and counting new null values

Tue, 17 Jan 2017 20:20:12 GMT

Worked again. Thank you so much Stefan!