topic Re: count based on 2 joined date fields where 1 is blank in QlikView

count based on 2 joined date fields where 1 is blank

isciberras — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I keep encountering an issue in Qlikview where I am unable to count the number of blank fields when the 2 fields I am comparing are joined in Qlikview and not both on the same spreadsheet.

Basically I have 2 date fields that I am comparing in Qlikview. Each date field is held separately in 2 spreadsheets which are joined together by an order reference and item.

What I am trying to achieve is a count of the Receipt Date where the receipt date is blank. So in the case I'm showing below, I would like to have a count of 2.

Date Order Raised	Receipt Date
02/12/2016	02/12/2016
02/12/2016	03/12/2016
03/12/2016	03/12/2016
04/12/2016	-
05/12/2016	-

Previously, when I loaded in all the data from one spreadsheet I was able to count the blank rows of the corresponding Receipt date in the load script, however this time round because I am joining both tables together I can't do a count on the blank fields as Qlikview will tell me that there are no blank fields in the column (Which makes sense as the column won't have any blanks, the blanks occur when i compare both dates in Qlikview)

Does anyone have any ideas around how I can solve this please? Any ideas would be greatly appreciated.

Thanks,

Isaac

Re: count based on 2 joined date fields where 1 is blank

avinashelite — Wed, 18 Oct 2017 10:02:24 GMT

try this expression in the text box

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

if(len(trim([Receipt Date]))=0,count([Receipt Date]))

Re: count based on 2 joined date fields where 1 is blank

pradosh_thakur — Wed, 18 Oct 2017 10:26:42 GMT

try this function if it helps nullcount()

http://help.qlik.com/en-US/sense/September2017/Subsystems/Hub/Content/ChartFunctions/CounterAggregationFunctions/nullcount.htm

regards

Pradosh

Re: count based on 2 joined date fields where 1 is blank

Anil_Babu_Samineni — Wed, 18 Oct 2017 10:28:59 GMT

May be TextCount() instead NullCount()

Re: count based on 2 joined date fields where 1 is blank

isciberras — Wed, 18 Oct 2017 10:30:59 GMT

Thank you for your help but it didn't work unfortunately, i think the statements above are counting the null values.

In this case I don't think I'm importing blank cells, I think the issue is that I'm trying to compare the 2 dates based on a field called order reference.

What i found is that if i import the data from 1 spreadsheet then i can count the NULLs and the answer would be correct. Because this time I'm joining in 2 tables which don't have nulls, counting nulls wont work. The nulls are created when i compare the 2 fields against each other

Re: count based on 2 joined date fields where 1 is blank

effinty2112 — Wed, 18 Oct 2017 10:33:57 GMT

Hi Isaac,

Maybe

Count([Date Order Raised]) - Count([Receipt Date])

will give you the number of orders for which there is no receipt date.

Regards

Andrew

Re: count based on 2 joined date fields where 1 is blank

avinashelite — Wed, 18 Oct 2017 10:34:34 GMT

Could you share the sample app and show us what your trying to do , so that we could help you

Re: count based on 2 joined date fields where 1 is blank

Anonymous — Wed, 18 Oct 2017 10:49:03 GMT

Try this

=NullCount([Receipt Date])

return the no of Null rows in given field(Recipt No)

Regards

Sahadev Patil

Re: count based on 2 joined date fields where 1 is blank

isciberras — Wed, 18 Oct 2017 10:54:23 GMT

Thanks you for your help Andrew, I tried doing this but unfortunately the answer isn't correct.

I will build and share a sample app to try better explain what is happening.

Thanks,

Isaac