How to supress the null values

[Anonymous]

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

[kaushiknsolanki]

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

[Anonymous]

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

[kaushiknsolanki]

Try adding a dimension to the chart.

Regards,

Kaushik Solanki

[Anonymous]

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.

[vinieme12]

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

[Anonymous]

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.

[vinieme12]

Do you mean load script?

then just add a where clause when loading the data

load *

FROM XXXX

where len(trim(name))>0;

[Anonymous]

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.

[qv_testing]

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 !