How to display a row containing null or 0 for a specific column ?
Article ID: KB0070852
Updated On:
| Products | Versions |
|---|---|
| ibi WebFOCUS | All versions |
Description
- Lets take an example to explain this.
- Look at the below data : (have attached the below data in the form of xlsx so that you can make the synonym and try the same )
- In total there are 4 types : Insurance, Agriculture, Sports, HealthCare
- There are only 2 types for for which 'entity' as 11 exists : Insurance and Sports .
- So if we try to display the data just for column 'entity' as 11 :
****************************
SET NODATA=0
TABLE FILE test12333
SUM VALUE
BY TYPE
ACROSS ENTITY
WHERE ENTITY EQ 99;
ON TABLE PCHOLD FORMAT HTML
END
****************************
SET NODATA=0
TABLE FILE test12333
SUM VALUE
BY TYPE
ACROSS ENTITY
WHERE ENTITY EQ 99;
ON TABLE PCHOLD FORMAT HTML
END
****************************
- Below is the result for the above procedure :
- As we can see not all the types are displayed except its displaying only for which the value exists in 'entity' : 11 .
Environment
Windows and Linux.
Resolution
- Now if we want to display all the types , and if the value doesn't exist for that specific entity , it should be treated as zero .
- So in-short, all the types should be displayed whether they have a value of zero or null (i.e. it doesn't exist )
- So we modify the procedure as below :
****************************
SET NODATA=0
TABLE FILE test12333
SUM VALUE
BY TYPE ROWS 'HealthCare' OVER 'Sports' OVER 'Insurance' OVER 'Agriculture'
WHERE ENTITY EQ 11;
ON TABLE PCHOLD FORMAT HTML
END
****************************
SET NODATA=0
TABLE FILE test12333
SUM VALUE
BY TYPE ROWS 'HealthCare' OVER 'Sports' OVER 'Insurance' OVER 'Agriculture'
WHERE ENTITY EQ 11;
ON TABLE PCHOLD FORMAT HTML
END
****************************
- Below is the output for the above procedure :
- So like this we have to mention the names of all the Types , by just writing OVER and then Type name.
Issue/Introduction
This article covers the instructions to display a row containing null or 0 for a specific column.
Additional Information
02207689
Feedback
Yes
No
Powered by
