Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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
next
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.