topic Null values and total percentage.. in QlikView

Null values and total percentage..

Fri, 02 Apr 2010 18:07:15 GMT

Hi,

I am a beginner and I really need help to solve these problems.

1.How to handle values which are displayed like -(Hypen) in my table box from a sql query.I have used

is not null with sql in where clause but still getting records with hypen values.

2.I want to display a Percentage of correct records(without null) over total records(including nulls)

3. Can we do an aggregation on the values which are displayed in statistics box

Please respond ASAP.

Thank You.

RP.

Null values and total percentage..

Wed, 07 Apr 2010 15:46:14 GMT

Hi Rajani,

The hyphen is displayed by default in a QlikView chart when the data value is null. You can change that behavior by right-clicking the chart and selecting Properties and selecting the Presentation tab. You'll see a place where you can define a null symbol and a missing symbol. I sometimes set the null symboll to the word null just to make the data issues clear.

The Statistics Box doesn't give you the kind of control you need (if I understand your question correctly) - it makes you select a field and doesn't allow you to enter an expression that would only include specified values of the field.

You could try a text object with expressions that include or exclude the data according to your requirements. For example, this text expression could be used in a text object to show information about a field named BUDGET but excluding null values:

='Total count = '&Count(total if(not isnull(BUDGET),BUDGET))&'
Sum = '&Sum(BUDGET)&'
Average = '&Round(Sum(BUDGET)/Count(if(not isnull(BUDGET),BUDGET)))&'
Min = '&min(if(not isnull(BUDGET),BUDGET))&'
Max = '&max(if(not isnull(BUDGET),BUDGET))

The If statement can be used to include or exclude any values. Use the Round function and Num functions as needed to format the data.

Null values and total percentage..

Wed, 07 Apr 2010 19:02:50 GMT

hi,

to handle values which are displayed like -(Hypen) in the table box

use

set nulldisplay=' - '

since you are importing data from sql database.

thanks

Null values and total percentage..

Wed, 07 Apr 2010 22:24:36 GMT

Thank You Tim.

Could you please write me a sample code to read a table box using Macro . I have a table box having 4 fields (Country,County,Collateral,Name)

I want to read table box for each field and check the Null values and incorrect values present for each field . If there are null values then the row should not be displayed in table box and a counter to count the total records which are not to be displayed

Country county collateral Name

USA FL Fire xyz

UK Bedford Wind ABC

et Doc = ActiveDocument

ActiveDocument.ClearAll(false)

set tb = ActiveDocument.Sheets("Main").SheetObjects("TB01")

set fld = tb.GetField(1)

msgbox(fld.Name)

For r = 1 to tb.GetRowCount-1

set cell = tb.GetCell(r,2)

msgbox(cell)

if cell is null or len(cell)<6 then

//statement to remove the record from table box.

set counter1 = counter1+1

end if

But the variable Cell is not holding the field value (USA). Please help me to read the content of each field in table box until end of the records.

Thank You

Regards,

Rajani.