Count of Missing Field Values

[qlikwiz123]

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

[vishsaggi]

Try this?

Table1:

CrossTable(Data, Values, 5)

LOAD A,

    B,

    C,

    D,

    Date(E) AS DateField,

    F,

    G,

    H,

    I,

    J,

    K,

    L,

    M,

    N,

    O,

    P

FROM

[..\CrossTable Data.xlsx]

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

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

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)

[vishsaggi]

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?

[qlikwiz123]

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

[vishsaggi]

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

[qlikwiz123]

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?

[vishsaggi]

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.

[qlikwiz123]

This is what I get 😐

[vishsaggi]

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

[qlikwiz123]

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