topic Re: How to supress the null values in QlikView

How to supress the null values

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

Hi,

I am facing one issue in vb script,

I need to export the pivot table data into excel i can export the data into excel by using vb script but, while exporting i need to supress the null values for example:

Qlikview Table:

Sno	name
1	aa
2	bb
3	-
4	dd
5	ee
6	ff
7	-
8	hh

After exporting this table by using vb script i need to supress the null values

My expected output:

Sno	name
1	aa
2	bb
4	dd
5	ee
6	ff
8	hh

Regards,

Yuvaraj

Re: How to supress the null values

kaushiknsolanki — Thu, 24 Aug 2017 06:56:13 GMT

Hi,

Suppress it in your chart it self. Go to Presentation tab and check "Suppress Zero Value" and "Suppress Missing".

For coding try below example.

set chart = ActiveDocument.Activesheet.CreatePivotTable

chart.AddDimension "ProductType"

chart.AddExpression "sum(Amount)"

chart.AddExpression "count(Customer)"

set cp = chart.GetProperties

cp.SuppressZero = true

chart.SetProperties cp

Regards,

Kaushik Solanki

Re: How to supress the null values

Anonymous — Thu, 24 Aug 2017 07:14:00 GMT

Thanks for your reply i tried what mention in code but getting error as "Unterminated string Constant".

My code:

MyDoc contains qvw path

set chart = MyDoc.GetSheetObject(CH41);

chart.AddExpression "My Expression"

set cp = chart.GetProperties

cp.SuppressZero = true

chart.SetProperties cp

Re: How to supress the null values

kaushiknsolanki — Thu, 24 Aug 2017 07:16:15 GMT

Try adding a dimension to the chart.

Regards,

Kaushik Solanki

Re: How to supress the null values

Anonymous — Thu, 24 Aug 2017 07:32:50 GMT

Showing error as "Object doesn't support this method"

'AddDimension'

what are dimensions i have used in expression that dimensions only used in AddDimension function.

Re: How to supress the null values

vinieme12 — Thu, 24 Aug 2017 08:26:53 GMT

Have you checked Suppress when value is null in the chart that you are exporting?

Re: How to supress the null values

Anonymous — Thu, 24 Aug 2017 09:30:29 GMT

I have pivot table the data contains suppress values like below,

Sno	name
1	aa
2	bb
3	-
4	dd
5	ee
6	ff
7	-
8	hh

In qlikview presentation tab if we check "Suppress Zero Values we can achieve that but i need to perform only in script level.

Re: How to supress the null values

vinieme12 — Thu, 24 Aug 2017 10:10:59 GMT

Do you mean load script?

then just add a where clause when loading the data

load *

FROM XXXX

where len(trim(name))>0;

Re: How to supress the null values

Anonymous — Thu, 24 Aug 2017 10:19:15 GMT

No i need to perform in vb script level, we can't do any actions in qvw document.

In qlikview itself we can do this but i need to perform in vb script.

Re: How to supress the null values

Nagaraju_KCS — Wed, 14 Mar 2018 11:41:36 GMT

Hi,

I'm not aware about much VB Script, but my suggestion.

create 2 charts, one chart leave everything NULL aslo (suppose object ID CH1)

and another one create with "supress when value is Null" , hide this chart in visual side, export from here

(suppose object ID CH2)

In Your VB script use second chart (CH2)

Thanks !