Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Display NoData value

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Display NoData value
 Login/Join
 
Silver Member
posted
Hi All,

Problem displaying nodata. I want my report to display the title if there is no data for the particular cilumn

eg;

 
DEFINE FILE CAR
ROW_NAME/A225V = IF SEATS EQ '1' THEN 'ROW1' ELSE IF SEATS EQ '2' THEN 'ROW2' ELSE IF SEATS EQ '3' THEN 'ROW3' ELSE '';
END
TABLE FILE CAR
PRINT
CNT.ROW_NAME
BY ROW_NAME
BY COUNTRY
WHERE COUNTRY EQ 'INDIA'
 

if in the above code if for INDIA there is no value for 1 and 2 i still want the reprot to display like below

row_name country count
ROW1 INDIA 0
ROW2 INDIA 0
ROW3 INDIA 6

This message has been edited. Last edited by: Kerry,


769 Excel,PDF and HTML
 
Posts: 35 | Location: Bangalore | Registered: March 27, 2008Report This Post
Virtuoso
posted Hide Post
This is not a question of NODATA as such. NODATA comes into play when a field within a record has not been assigned a value. What you are looking for is displaying entire records that are not existing. That requires a totally different approach. You would have to invent a means of providing those non-existing records and add them to the report. It is possible to do this using, for instance, the MATCH command.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
<JG>
posted
quote:
WHERE COUNTRY EQ 'INDIA'

Nail on the head as we say.

That = 0 lines;

MATCH will not help as you have nothing to MATCH against.

By having that WHERE clause you are effectively saying to WebFOCUS
DO NOT SELECT ANY DATA.

As GamP says this is not a question of NODATA it is actually really a No Data
Situation, which are 2 very different things.

I know that on the forum we regularly ask for a repro against standard
WF sample files (CAR in particular)

But in this case it is totally invalid because of the WHERE.

Very basically WebFOCUS works like this.
1. Generate the focexec.
2. Run the focexec.
3. Translate the focexec into the required language for the adapter,
(oracle sql, Mssql, MDX etc.)
4. Return the rows that meet any selection criteria passed to the adapter.
5. Process the returned rows in any DEFINE
6. Accumulate the rows based on the verb (PRINT, SUM)
7. Perform any COMPUTE’s on the accumulated answer set.
8. Deliver the output.

There is potentially a lot more going on but that’s the absolute basic.

For you with this example the WHERE clause means that the process fails at step 4 simply because no data rows are returned.
 
Report This Post
Platinum Member
posted Hide Post
Try this
DEFINE FILE CAR
ROW_NAME/A225V = IF SEATS EQ '1' THEN 'ROW1' ELSE IF SEATS EQ '2' THEN 'ROW2' ELSE IF SEATS EQ '3' THEN 'ROW3' ELSE '';
END
TABLE FILE CAR
PRINT
CNT.ROW_NAME
FOR ROW_NAME
ROW1 OVER
ROW2 OVER
ROW3
WHERE COUNTRY EQ 'INDIA'
END
 
Posts: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report This Post
<JG>
posted
Naturally you tested that before posting it.

quote:

NUMBER OF RECORDS IN TABLE= 0 LINES= 0
 
Report This Post
Gold member
posted Hide Post
Shru - is there any sort of join prior to your report that may affect your define or missing data?

Otherwise, if I understand correctly, you might be able to use ROWS...OVER like this:

BY ROW_NAME ROWS ROW1 OVER ROW2 OVER ROW3

This creates "buckets" for all possible values in your BY field whether there is data or not.

In addition, if you use this and are exporting to Excel and want to set your NODATA value, you need to set another command:
SET EMPTYCELLS = OFF
SET NODATA = [your value]

Bob


WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8
 
Posts: 93 | Registered: February 20, 2008Report This Post
<JG>
posted
Bob read the last 2 posts
 
Report This Post
Platinum Member
posted Hide Post
JG,
There are zero records because there is no data in the CAR file for 'INDIA' not because the code is wrong.

Try
If COUNTRY EQ 'ENGLAND'
and BTW change the PRINT to a SUM for a more meaningful result.

The point was to show the use of OVER to create rows in the report even when data does not exist in the file being reported from.
 
Posts: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report This Post
<JG>
posted
quote:
there is no data in the CAR file for 'INDIA'


That's exactly the point.

Shru wants to generate an output when there are no rows
 
Report This Post
Platinum Member
posted Hide Post
OK but what's the point of that?
 
Posts: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report This Post
Guru
posted Hide Post
If there always has to be output for certain values, even if there is no data, you might want to create a control file with all the possible values that must appear (countries in this case). You then do an outer join between your control file and your data.


(Prod: WebFOCUS 7.7.03: Win 2008 & AIX hub/Servlet Mode; sub: AS/400 JDE; mostly Self Serve; DBs: Oracle, JDE, SQLServer; various output formats)
 
Posts: 391 | Location: California | Registered: April 14, 2003Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Display NoData value

Copyright © 1996-2020 Information Builders