topic How to Create Null and Not Null Columns in QlikView

How to Create Null and Not Null Columns

Wed, 09 Oct 2013 02:49:00 GMT

Hi,

I'm new to qlikview,

I have 12 fields ( SKU, PRODUCT_TYPE, LICENSE, BAND, etc..) each with different values or blanks.

I'm trying to create a pivot table or chart that gives me a count of all the null for each field and count of all the values for each field.

I can't seem to get this all on one table. I can have one table that gives me the nullcount for each field (done manually by writing each expression for each field). But I can't get a table that have the fields as rows and null/not null be two columns.

Can anyone help me or point me in the right direction?

Thanks a lot!@

Re: How to Create Null and Not Null Columns

saumyashah90 — Wed, 09 Oct 2013 04:00:28 GMT

Can You explain more clearly?

Re: How to Create Null and Not Null Columns

sujeetsingh — Wed, 09 Oct 2013 05:28:34 GMT

You can use system fields for this you can use $Field as dimension .

Go for cross join

Re: How to Create Null and Not Null Columns

Wed, 09 Oct 2013 07:07:11 GMT

Hi ! So I am pulling in 13 columns from an excel . And I want to count the null and not nulls for each column.

So I have a whole bunch of product skus and each of these skus have 12 attributes . Sku number and each of these attributes are a column in my data table .

So for example the chart I want to be able to have a count how many null and not null values i have for each of these attributes. Either in bar graph form or table form. Let me know if that clarifies it . If not ill try to give more detail!! Many thanks

Re: How to Create Null and Not Null Columns

saumyashah90 — Wed, 09 Oct 2013 07:09:39 GMT

Count(FieldName=' ')

and Count(Not IsNull(FieldName))

Re: How to Create Null and Not Null Columns

Gysbert_Wassenaar — Wed, 09 Oct 2013 07:10:45 GMT

You can try the solution explained here: nullcounts of all fields

But it will be much easier if you replace the nulls with real values like '<NULL>'. See this document: NULL handling in QlikView.

Re: How to Create Null and Not Null Columns

Wed, 09 Oct 2013 07:12:52 GMT

My data table looks like this, 12 attributes for each sku #.

sku product_type meter band .......

123 consumer yes a

345 - no -

567 enterprise - -

and I want a table that looks like this, where it counts each attribute of how many null values and not null values it has.

null count not null count

product_type 100 200

Meter 50 50

Band 75 25

...

etc...for all 12 attributes

Please help!

Re: How to Create Null and Not Null Columns

Wed, 09 Oct 2013 07:14:48 GMT

Hi do I put that in the actual script or write expressions for each in the table properties?

Because I end up only getting something that looks like

null count

product_type 100

Meter 50

Band 75

and I can't get the second column in there

Re: How to Create Null and Not Null Columns

Wed, 09 Oct 2013 07:15:54 GMT

Hi! how do I do a cross join? What am I joining? I have all the data on one table already.

Re: How to Create Null and Not Null Columns

saumyashah90 — Wed, 09 Oct 2013 07:19:41 GMT

yeah so what you can do is

In expression

1)Count(product_type=' ')

2)Count(Not IsNull(product_type))

Re: How to Create Null and Not Null Columns

saumyashah90 — Wed, 09 Oct 2013 07:19:52 GMT

and so on

Re: How to Create Null and Not Null Columns

saumyashah90 — Wed, 09 Oct 2013 07:21:11 GMT

or else i will say you out all the fields in on column say

Load * Inline [

Fields,

Sku,

Product_type,

band,

meter,

etc

];

Re: How to Create Null and Not Null Columns

Wed, 09 Oct 2013 16:31:59 GMT

hi what but I end up getting will be something that looks like this

product type 6000

product type 500

rather than

null not null

product type 6000 500

am I doint it wrong? Should those two expressions be in a single expression? if so, i'm not really sure how to do it?

Re: How to Create Null and Not Null Columns

Wed, 09 Oct 2013 16:33:51 GMT

Is this feasible for a large data set?

Re: How to Create Null and Not Null Columns

saumyashah90 — Thu, 10 Oct 2013 04:17:27 GMT

1)if you load all attributes in a column then yes it is feasible.

2)either load it from a excel to make it more error free and dynamic(no change of code).