Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
When a dimension has no value (or no value is associated to current selections), a hypercube contains no rows.
However, when a dimension attribute is defined, the hypercube contains one row with NULL value.
No combination of qSuppressZero, qSuppressMissing, qNullSuppression or qAttribute values will remove that extra row.
This can be demonstrated using following load script:
emptyTable:
LOAD * INLINE [emptyDimension];
By creating a straight table visualization with the emptyDimension field used as dimension.
Initially, the table is empty. If I add a Background color expression ='red' a NULL row appears in the table.
How to get rid of that null row?
@tomas_janco yeah using 'qSuppressZero' should have done it, what does your code look like?
Here are the properties of the table object, qSuppressZero is true (set by disabling "Include zero values" in Data Handling), the null row is visible.
{
"qInfo": {
"qId": "WGSKDWP",
"qType": "table"
},
"qExtendsId": "",
"qMetaDef": {},
"qStateName": "",
"qHyperCubeDef": {
"qStateName": "",
"qDimensions": [
{
"qLibraryId": "",
"qDef": {
"qGrouping": "N",
"qFieldDefs": [
"emptyDimension"
],
"qFieldLabels": [
""
],
"qSortCriterias": [
{
"qSortByState": 0,
"qSortByFrequency": 0,
"qSortByNumeric": 1,
"qSortByAscii": 1,
"qSortByLoadOrder": 1,
"qSortByExpression": 0,
"qExpression": {
"qv": ""
},
"qSortByGreyness": 0
}
],
"qNumberPresentations": [],
"qReverseSort": false,
"qActiveField": 0,
"qLabelExpression": "",
"autoSort": true,
"cId": "RkuD",
"othersLabel": "Others",
"textAlign": {
"auto": true,
"align": "left"
},
"representation": {
"type": "text",
"urlPosition": "dimension",
"urlLabel": "",
"linkUrl": "",
"imageSetting": "label",
"imageLabel": "",
"imageUrl": "",
"imageSize": "fitHeight",
"imagePosition": "topCenter"
}
},
"qNullSuppression": true,
"qIncludeElemValue": false,
"qOtherTotalSpec": {
"qOtherMode": "OTHER_OFF",
"qOtherCounted": {
"qv": "10"
},
"qOtherLimit": {
"qv": "0"
},
"qOtherLimitMode": "OTHER_GE_LIMIT",
"qSuppressOther": false,
"qForceBadValueKeeping": true,
"qApplyEvenWhenPossiblyWrongResult": true,
"qGlobalOtherGrouping": false,
"qOtherCollapseInnerDimensions": false,
"qOtherSortMode": "OTHER_SORT_DESCENDING",
"qTotalMode": "TOTAL_OFF",
"qReferencedExpression": {
"qv": ""
}
},
"qShowTotal": false,
"qShowAll": false,
"qOtherLabel": {
"qv": "Others"
},
"qTotalLabel": {
"qv": ""
},
"qCalcCond": {
"qv": ""
},
"qAttributeExpressions": [
{
"qExpression": "='red'",
"qLibraryId": "",
"qAttribute": true,
"qNumFormat": null,
"qLabel": "",
"qLabelExpression": "",
"id": "cellBackgroundColor"
}
],
"qAttributeDimensions": [],
"qCalcCondition": {
"qCond": {
"qv": ""
},
"qMsg": {
"qv": ""
}
}
}
],
"qMeasures": [],
"qInterColumnSortOrder": [
0
],
"qSuppressZero": true,
"qSuppressMissing": true,
"qInitialDataFetch": [],
"qReductionMode": "N",
"qMode": "S",
"qPseudoDimPos": -1,
"qNoOfLeftDims": -1,
"qAlwaysFullyExpanded": false,
"qMaxStackedCells": 5000,
"qPopulateMissing": false,
"qShowTotalsAbove": false,
"qIndentMode": false,
"qCalcCond": {
"qv": ""
},
"qSortbyYValue": 0,
"qTitle": {
"qv": ""
},
"qCalcCondition": {
"qCond": {
"qv": ""
},
"qMsg": {
"qv": ""
}
},
"qColumnOrder": [
0
],
"qExpansionState": [],
"qDynamicScript": [],
"qContextSetExpression": "",
"columnOrder": [
0
],
"columnWidths": [
-1
],
"qLayoutExclude": {
"qHyperCubeDef": {
"qStateName": "",
"qDimensions": [],
"qMeasures": [],
"qInterColumnSortOrder": [],
"qSuppressZero": false,
"qSuppressMissing": false,
"qInitialDataFetch": [],
"qReductionMode": "N",
"qMode": "S",
"qPseudoDimPos": -1,
"qNoOfLeftDims": -1,
"qAlwaysFullyExpanded": false,
"qMaxStackedCells": 5000,
"qPopulateMissing": false,
"qShowTotalsAbove": false,
"qIndentMode": false,
"qCalcCond": {
"qv": ""
},
"qSortbyYValue": 0,
"qTitle": {
"qv": ""
},
"qCalcCondition": {
"qCond": {
"qv": ""
},
"qMsg": {
"qv": ""
}
},
"qColumnOrder": [],
"qExpansionState": [],
"qDynamicScript": [],
"qContextSetExpression": ""
}
}
},
"script": "",
"search": {
"sorting": "auto"
},
"showTitles": true,
"title": "",
"subtitle": "",
"footnote": "",
"disableNavMenu": false,
"showDetails": false,
"showDetailsExpression": false,
"totals": {
"show": true,
"position": "noTotals",
"label": "Totals"
},
"scrolling": {
"horizontal": true,
"keepFirstColumnInView": false,
"keepFirstColumnInViewTouch": false
},
"multiline": {
"wrapTextInHeaders": true,
"wrapTextInCells": true
},
"visualization": "table"
}
The layout says the dimension has 0 values, but the hypercube has size of 1x1
{
"qInfo": {
"qId": "WGSKDWP",
"qType": "table"
},
"qMeta": {
"privileges": [
"read",
"update",
"delete",
"exportdata"
]
},
"qSelectionInfo": {},
"qHyperCube": {
"qSize": {
"qcx": 1,
"qcy": 1
},
"qDimensionInfo": [
{
"qFallbackTitle": "emptyDimension",
"qApprMaxGlyphCount": 1,
"qCardinal": 0,
"qSortIndicator": "A",
"qGroupFallbackTitles": [
"emptyDimension"
],
"qGroupPos": 0,
"qStateCounts": {
"qLocked": 0,
"qSelected": 0,
"qOption": 0,
"qDeselected": 0,
"qAlternative": 0,
"qExcluded": 0,
"qSelectedExcluded": 0,
"qLockedExcluded": 0
},
"qTags": [],
"qDimensionType": "D",
"qGrouping": "N",
"qNumFormat": {
"qType": "U",
"qnDec": 0,
"qUseThou": 0
},
"qIsAutoFormat": true,
"qGroupFieldDefs": [
"emptyDimension"
],
"qMin": "NaN",
"qMax": "NaN",
"qAttrExprInfo": [
{
"qMin": "NaN",
"qMax": "NaN",
"qFallbackTitle": "='red'",
"qNumFormat": {
"qType": "U",
"qnDec": 0,
"qUseThou": 0
},
"qIsAutoFormat": true,
"id": "cellBackgroundColor"
}
],
"qAttrDimInfo": [],
"qCardinalities": {
"qCardinal": 0,
"qHypercubeCardinal": 1,
"qAllValuesCardinal": -1
},
"autoSort": true,
"cId": "RkuD",
"othersLabel": "Others",
"textAlign": {
"auto": true,
"align": "left"
},
"representation": {
"type": "text",
"urlPosition": "dimension",
"urlLabel": "",
"linkUrl": "",
"imageSetting": "label",
"imageLabel": "",
"imageUrl": "",
"imageSize": "fitHeight",
"imagePosition": "topCenter"
}
}
],
"qMeasureInfo": [],
"qEffectiveInterColumnSortOrder": [
0
],
"qGrandTotalRow": [],
"qDataPages": [],
"qPivotDataPages": [],
"qStackedDataPages": [],
"qMode": "S",
"qNoOfLeftDims": -1,
"qTreeNodesOnDim": [],
"qColumnOrder": [
0
],
"columnOrder": [
0
],
"columnWidths": [
-1
]
},
"script": "",
"search": {
"sorting": "auto"
},
"showTitles": true,
"title": "",
"subtitle": "",
"footnote": "",
"disableNavMenu": false,
"showDetails": false,
"showDetailsExpression": false,
"totals": {
"show": true,
"position": "noTotals",
"label": "Totals"
},
"scrolling": {
"horizontal": true,
"keepFirstColumnInView": false,
"keepFirstColumnInViewTouch": false
},
"multiline": {
"wrapTextInHeaders": true,
"wrapTextInCells": true
},
"visualization": "table"
}