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] Dates + dense data
Go
New
Search
Notify
Tools
Reply
  
[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, 2012Reply With QuoteReport 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, 2003Reply With QuoteReport 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: 5682 | Location: United Kingdom | Registered: April 08, 2004Reply With QuoteReport 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


WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
 
Posts: 173 | Registered: October 19, 2010Reply With QuoteReport 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: 1954 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Danny, can you post links to these?
quote:
I have the original McGuyver document files
Shalom...
 
Posts: 3084 | Location: Middle Tennessee | Registered: February 23, 2005Reply With QuoteReport 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: 1954 | Location: Tel Aviv, Israel | Registered: March 23, 2006Reply With QuoteReport 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, 2012Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Dates + dense data

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