topic Re: Count of Missing Field Values in QlikView

Count of Missing Field Values

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

I have Excel Data and I need to find two Output fields

1. Count of all the values

2. Count of Missing values.

I tried using the CrossTable approach but the field names are not the right ones. How to achieve this in QlikView? I need to create Total and Missing Fields

Re: Count of Missing Field Values

vishsaggi — Mon, 01 Oct 2018 15:22:39 GMT

Try this?

Table1:

CrossTable(Data, Values, 5)

LOAD A,

Date(E) AS DateField,

FROM

[..\CrossTable Data.xlsx]

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Col, Pos(Top, 17)),

Remove(Col, Pos(Top, 17))

));

Add pivot table and add field A to DateField like below as your dimensions and

add expr like

= Sum(Values)

= Sum({<Values = {''}>}Values)

Re: Count of Missing Field Values

vishsaggi — Mon, 01 Oct 2018 15:54:16 GMT

Your excel sheet has a blank column and your Total and Missing Fields pre calculated so to remove those three cols i have removed them using transformation wizard. Yes just create another bar chart with same expressions and add what ever dimension you want to add. What you mean by field names can you tell your expected output?

Re: Count of Missing Field Values

qlikwiz123 — Thu, 04 Oct 2018 17:26:12 GMT

Hi,

For some reason, I don't see all the values in the columns A to P in the front-end. Looks like you built a chart using the data. Could I please have that qvw file so that I can look into it and see what's the issue is? Would be really helpful.

Thanks

Re: Count of Missing Field Values

vishsaggi — Thu, 04 Oct 2018 19:48:51 GMT

Using cross table i created a new Field "Values" which holds all the 1's. I.e., cols F - P. Hope you got me.

Re: Count of Missing Field Values

qlikwiz123 — Thu, 04 Oct 2018 20:47:36 GMT

The Values Field only has value '1'. Counting Nulls in it using Sum({<Values = {''}>}Values) or sum(if(IsNull(Values),Values)) or anything else will always be zero. Any other way to count the missing/null values from the given data?

Re: Count of Missing Field Values

vishsaggi — Thu, 04 Oct 2018 20:58:43 GMT

Strange so which excel sheet are you loading. I have loaded the excel file you attached to this thread and the script i sent you is what i ran in my local qvw file. It worked fine for me, unless your excel sheet data got changed.

Re: Count of Missing Field Values

qlikwiz123 — Tue, 09 Oct 2018 14:27:14 GMT

This is what I get 😐

Re: Count of Missing Field Values

vishsaggi — Tue, 09 Oct 2018 14:29:06 GMT

Can you share excel sheet of this qvw file you are using and also share this qvw file please?

Re: Count of Missing Field Values

qlikwiz123 — Tue, 09 Oct 2018 14:37:59 GMT

Sure. Please find them attached. Make sure the Path in the QVW is right before you run.

Re: Count of Missing Field Values

vishsaggi — Tue, 09 Oct 2018 15:01:19 GMT

My bad can you use below expr for empty values like below

= Sum(Values)

= Count({<Values = {''}>}Values)

Check attached.

Re: Count of Missing Field Values

qlikwiz123 — Tue, 09 Oct 2018 15:14:54 GMT

Thank you for the quick action Let me check this one.

Re: Count of Missing Field Values

jayanttibhe — Tue, 09 Oct 2018 15:15:33 GMT

Hey buddy,

What you need is the Horizontal Calculations. So keep it simple and there is no need to add any Crosstables. Use the 'Range' functions. PFA is the solution.

Thanks

Re: Count of Missing Field Values

qlikwiz123 — Tue, 09 Oct 2018 15:41:03 GMT

Hi Jayant. Thank you so much. This function is awesome. We never realize how many functions we have and how to use them. Although Cross Table is a bit easier since it runs in the back-end script and you don't need to fill in the field names manually within the function, it's good to know this function exists Really appreciate your help. I just learned something new.

Re: Count of Missing Field Values

jayanttibhe — Tue, 09 Oct 2018 15:49:55 GMT

Thanks and Glad you like it:

There are following Range functions you can have a look :

Counter range functions

ClosedRangeCount

ClosedRangeMissingCount

ClosedRangeNullCount

ClosedRangeNumericCount

ClosedRangeTextCount

And Dont worry you can use them all in SCRIPT as well.

If you are happy with the solution then close this thread marking the correct answer.