Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]How to show Records when they don't exist
Go
New
Search
Notify
Tools
Reply
  
[SOLVED]How to show Records when they don't exist
 Login/Join
 
Member
posted
Hi,
Hoping this is an easy one..
I defined a field in car file called
SEATSA, it is simply spelling out each of the seats:
SEATSA/A5=IF SEATS EQ 2 THEN 'TWO' ELSE IF SEATS EQ '4' THEN 'FOUR' ELSE 'FIVE';

I now want to count seats BY CAR BY SEATSA, but I want to see all of the SEATSA values for each CAR.
For example for Alpha Romeo I want to see FIVE count 0, FOUR Count 1, TWO Count 2.

Here is my code:
DEFINE FILE CAR
  SEATSA/A12=IF SEATS EQ 2 THEN 'TWO' ELSE IF SEATS EQ '4' THEN 'FOUR'  ELSE 'FIVE';
END
TABLE FILE CAR
SUM CNT.SEATS
BY CAR
BY SEATSA
WHERE CAR EQ 'ALFA ROMEO';
END  


I am not getting the value of FIVE of course, since 5 does not exist for Alpha Romeo.
How do I show FIVE and have a count of 0?

Thank you.

This message has been edited. Last edited by: nickz,
 
Posts: 6 | Registered: September 13, 2019Reply With QuoteReport This Post
Gold member
posted Hide Post
Hi Nick,
You could try something like this.

DEFINE FILE CAR
  SEATS_2/I2= IF SEATS EQ 2 THEN 1 ELSE 0;
  SEATS_4/I2= IF SEATS EQ 4 THEN 1 ELSE 0;
  SEATS_5/I2= IF SEATS EQ 5 THEN 1 ELSE 0;
END
TABLE FILE CAR
SUM CAR NOPRINT
    SEATS_2 AS 'TWO'
    SEATS_4 AS 'FOUR'
    SEATS_5 AS 'FIVE'
BY CAR
END

 
Posts: 89 | Location: Oklahoma City, Oklahoma | Registered: July 01, 2010Reply With QuoteReport This Post
Silver Member
posted Hide Post
nickz, can you try this if it works for you?

 
SET NODATA = 0

TABLE FILE CAR
SUM CNT.SEATS
BY CAR
BY SEATS AS 'SEATS' ROWS 2 AS 'TWO' OVER 3 AS 'THREE' OVER 4 AS 'FOUR' OVER 5 AS 'FIVE' 
WHERE CAR EQ 'ALFA ROMEO';
END 
 


Thanks,
Pku

Focus, WebFocus 8105 on Windows
 
Posts: 33 | Registered: May 28, 2008Reply With QuoteReport This Post
Member
posted Hide Post
Thank you Pku,
This solution is exactly what I was looking for.
 
Posts: 6 | Registered: September 13, 2019Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]How to show Records when they don't exist

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.