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     [SOLVED] Dates + dense data

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Dates + dense data
 Login/Join
 
Member
posted
Hi,

I've seen this topic dense data or nodata and I get it, but what i'm planning to do is a bit differente.

I want to report all information between 2 dates, but the database doesn't have information for all days within those dates. So I would like to show days with no data as well on my graph. From what I understand from the topic above I would need to create a table with all dates needed, how can I do that?


Thanks in advance,
Pedro

WF 7702, WFDevTools 7702, Unix

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


WF 8.0.02 @ Linux environment
 
Posts: 12 | Registered: May 15, 2012Report This Post
Expert
posted Hide Post
Pedro, its called the McGyver technique, and its a Focus classic.
you make a file of all dates that you want
then join your data to it.
read up on mcGyver, there are articles on the TIps & Techniques pages.




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Expert
posted Hide Post
As for creating the file on the fly to match the dates you want, just search (above) on "tony daterng" and look at the first link you get listed.

T



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
This might be simpler for you if you don't mind using ggsales sample table.
-DEFAULT &start_date  = '20120401';
-DEFAULT &num_of_days = 30;
 APP PATH IBISAMP
 TABLE FILE GGSALES
 SUM COMPUTE DATEYYMD/YYMD = IF LAST DATEYYMD EQ 0 
                             THEN '&start_date' 
                             ELSE LAST DATEYYMD + 1;
    BY SEQ_NO NOPRINT
 WHERE SEQ_NO LE &num_of_days
 ON TABLE HOLD AS DATELIST FORMAT FOCUS
 END


WebFOCUS 8.2.06
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report This Post
Virtuoso
posted Hide Post
Pedro,
I have the original McGuyver document files:
McGuyver Techniques PDF
McGuyver still lives PPT

I can send them to you.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Expert
posted Hide Post
Danny, can you post links to these?
quote:
I have the original McGuyver document files
Shalom...
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Virtuoso
posted Hide Post
Doug,

I have them on my machine.
Give me an e-mail address and I'll send them to you.
Ah! I found it on your profile. On its way...
Shabbat Shalom,


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Member
posted Hide Post
Hi,

Thank you all for your replies and please forgive me for my late reply. I manage to create something similar to what Tony said, I think, as for the MacGyver technique I've seen it mentioned a lot of times but I haven't had the privilege to find it. So, @Danny if you can send me the files directly I would appreciate, see my profile for email address.

This is how I managed to solve my question:

-SET &begin_interval = '2012-03-01';
-SET &end_interval = '2012-03-31';
-SET &column_name = 'DATE_VALUE';

-*get all data available on my table and hold it

TABLE FILE TABLE_NAME
SUM *
WHERE &column_name FROM &begin_interval TO &end_interval
ON TABLE HOLD FORMAT FOCUS AS DATA_DAILY
END

-* I went to my databse to get all dates within my interval

ENGINE SQLORA SET DEFAULT_CONNECTION &defaultconn
SQL SQLORA PREPARE SQLALLDATE FOR
SELECT TO_CHAR(TO_DATE('&begin_interval.EVAL', 'YYYY-MM-DD') - 1 + rownum,'YYYYMMDD') AS &column_name
FROM all_objects
WHERE TO_DATE('&begin_interval.EVAL', 'YYYY-MM-DD') - 1 + rownum <= TO_DATE['&end_interval.EVAL', 'YYYY-MM-DD')
END

-*then printed everything as my dates table
TABLE FILE SQLALLDATE
PRINT &column_name
ON TABLE HOLD AS ALLDATE
END

-*then merge everything

MATCH FILE ALLDATE
PRINT &column_name
BY &column_name
RUN
FILE DATA_DAILY
PRINT *
BY &column_name
AFTER MATCH HOLD AS NEW_DATA_DAILY OLD
END

And that's it!

Once again, thank you.
Pedro

PS: Achtung!! DATE_VALUE from DATA_DAILY and from SQLALLDATE have to be exactly of the same type and format so that the merge can understand that are the same.


WF 8.0.02 @ Linux environment
 
Posts: 12 | Registered: May 15, 2012Report 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     [SOLVED] Dates + dense data

Copyright © 1996-2020 Information Builders