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] ADDING ROWS TO HOLD FILE

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] ADDING ROWS TO HOLD FILE
 Login/Join
 
Member
posted
i am doing a BY date of the data.
I am looking for data for 100 days from the START_DATE.
But there is no data for some of the days.
I want to know how to display data for 100 days ,if no records then display as NULL
Please reply immediately

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


WebFOCUS 7.69
Windows
Excel, HTML, PDF,AHTML
 
Posts: 14 | Registered: November 16, 2009Report This Post
Expert
posted Hide Post
Have you search the forum for missing row techniques?

One way is to use a Dialogue Manager REPEAT loop to generate a file with all of the dates that you need into a temp file then join this file (left-outer) to the data file.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Report This Post
Virtuoso
posted Hide Post
Adithya,

Here is another technique that does essentially what Ginny suggests, but does not involve the use of Dialogue Manager to produce the list of dates. I use the CAR file in my example, but you must use a file that contains at least 100 rows (the CAR file does not) in order to generate a list of 100 consecutive dates (perhaps you can use the same table that contains your data). You may need to change the date formats I use here to correspond to the date format in your table.

-SET &START_DATE = '01/01/2009';
-*
DEFINE FILE CAR
START_DATE/MDYY WITH BODYTYPE = '&START_DATE';
TEMP_DATE/MDYY = IF START_DATE GT (LAST TEMP_DATE + 1) THEN START_DATE ELSE (LAST TEMP_DATE + 1);
END
-*
-* This will produce a hold file containing a list of 100 consecutive dates
-* (less than 100 in this case because the CAR file does not contain 100 records).
TABLE FILE CAR
PRINT TEMP_DATE NOPRINT
BY TEMP_DATE
WHERE RECORDLIMIT EQ 100
ON TABLE HOLD AS HOLDDATE
END
-*
-* Extract the data you need for the report.
TABLE FILE <your tablename>
PRINT/SUM <column names>
BY DATE
WHERE DATE GE '&START_DATE';
ON TABLE HOLD AS HOLDDATA
END
-*
-* Join your data output file to the hold file containing the list of 100 dates.
JOIN CLEAR *
JOIN TEMP_DATE IN HOLDDATE TO DATE IN HOLDDATA AS J1
-*
TABLE FILE HOLDDATE
PRINT <column names>
BY TEMP_DATE
ON TABLE SET ALL ON
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Expert
posted Hide Post
You could also use the FOR statement or ROWS.
But this requires some data to exist.
-SET &STRTDATE = '800601' ;
TABLE FILE EMPLOYEE
PRINT CURR_SAL
FOR HIRE_DATE
-SET &Over = ' ' ;
-REPEAT DATELOOP FOR &CNTR FROM 0 TO 99 ;
-SET &NEWDATE = AYMD(&STRTDATE,&CNTR,'I6');
 &Over &NEWDATE
-SET &Over = 'OVER' ;
-DATELOOP
ON TABLE HOLD AS TMP_DATA
END

DEFINE FILE TMP_DATA
 HIRE_DATE/I6YMD = EDIT(E01);
END

TABLE FILE TMP_DATA
PRINT CURR_SAL
BY HIRE_DATE
END


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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] ADDING ROWS TO HOLD FILE

Copyright © 1996-2020 Information Builders