Focal Point
[SOLVED] Dates + dense data

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/2747036126

June 25, 2012, 12:04 PM
pferreira
[SOLVED] Dates + dense data
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
June 25, 2012, 12:53 PM
susannah
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
June 25, 2012, 01:01 PM
Tony A
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 
June 26, 2012, 05:51 PM
dbeagan
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
June 27, 2012, 01:28 PM
Danny-SRL
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

June 28, 2012, 09:09 AM
Doug
Danny, can you post links to these?
quote:
I have the original McGuyver document files
Shalom...
June 29, 2012, 08:52 AM
Danny-SRL
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

July 04, 2012, 10:31 AM
pferreira
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