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] Populate next months with same information

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Populate next months with same information
 Login/Join
 
Silver Member
posted
I have 2 tables class_sales and month

class_sales
class_id fcst_sales_amt
11111111 200,000
22222222 300,000

month
month_id
201001
201002
201003
201004
201005
201006
201007
201008
201009
201011
201012

What I would like to cerate is a report that will look like this:

month_id class_id fcst_sales_amt
201001 11111111 200,000
201002 11111111 200,000
201003 11111111 200,000
201004 11111111 200,000
201005 11111111 200,000
201006 11111111 200,000
201007 11111111 200,000
201008 11111111 200,000
201009 11111111 200,000
201011 11111111 200,000
201012 11111111 200,000
201001 22222222 300,000
201002 22222222 300,000
201003 22222222 300,000
201004 22222222 300,000
201005 22222222 300,000
201006 22222222 300,000
201007 22222222 300,000
201008 22222222 300,000
201009 22222222 300,000
201011 22222222 300,000
201012 22222222 300,000

Any help would be greatly appreciated.

Thanks,
Joel

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


7.6.6
Windows 2003 Server, Sybase IQ, DB2, SQL Server and Oracle Databases
Excel, HTML and PDF
 
Posts: 34 | Registered: February 20, 2008Report This Post
Virtuoso
posted Hide Post
Do the two tables have anything in commmon...the one only has month?
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Silver Member
posted Hide Post
No, the two tables are distinct and nothing in common.

Basically, I just want to create a report that will populate the same information through out the year (different months same forecast sales).

in SQL it would look like this:

select a.month_id, b.class_id, b.fcst_sales_amt
from month a, class_sales b

no where clause ...


7.6.6
Windows 2003 Server, Sybase IQ, DB2, SQL Server and Oracle Databases
Excel, HTML and PDF
 
Posts: 34 | Registered: February 20, 2008Report This Post
Expert
posted Hide Post
You could try a conditional join.

JOIN FILE VIDEOTRK AT MOVIECODE TAG V1 TO ALL 
     FILE MOVIES   AT RELDATE   TAG M1 AS JW1
-*  WHERE V1.MOVIECODE EQ M1.MOVIECODE;
END
TABLE FILE VIDEOTRK
 PRINT LASTNAME
 TITLE
END


Just don't specify a WHERE clause


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
Virtuoso
posted Hide Post
MATCH might do it.

DEFINE FILE MONTH
 DUMMY/A1 = 'X';
END
-*
DEFINE FILE CLASS_SALES
 DUMMY/A1 = 'X';
END
-*
MATCH FILE MONTH
 PRINT MONTH_ID
 BY DUMMY
RUN
FILE CLASS_SALES
 SUM FCST_SALES_AMT
 BY DUMMY
 BY CLASS_ID
AFTER MATCH HOLD OLD-OR-NEW
END

EDIT: Correction.


Or maybe simply:

MATCH FILE MONTH
 PRINT MONTH_ID
 BY MONTH_ID
RUN
FILE CLASS_SALES
 SUM FCST_SALES_AMT
 BY CLASS_ID
AFTER MATCH HOLD OLD-OR-NEW
END

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
As your future months will have zero values, you can compute the value using LAST.

im assuming you will have sales for say 201001 & 201002 & want to retain the sales in 201002 to populate future months.

compute newsales/d15c=if sales eq 0 then last sales else sales;

Ive had to do quite a bit with this recently to populate our income forcasting grpahs/tables etc

It gets a bit more 'fun' when you have to do this for many individuals with multiple forecasting streams.


81.05 All formats
 
Posts: 56 | Location: Manchester | Registered: November 21, 2006Report This Post
Platinum Member
posted Hide Post
Hi Joel,

Here's an example where you HOLD both tables with a common "DUMMY_KEY" and then JOIN the hold files.

-*
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE +++ CREATE FILE - CLASS_SALES +++
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-* CREATE DATA
FILEDEF SALES DISK class_sales_data.txt
-RUN
-WRITE SALES 11111111 200000
-WRITE SALES 22222222 300000
-CLOSE
-RUN
-* CREATE MFD
FILEDEF MASTER DISK class_sales.mas
-RUN
-WRITE MASTER FILE=CLASS_SALES, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=CLASS_SALES, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=CLASS_ID, ALIAS=CLASS_ID, USAGE=A8, ACTUAL=A08, $
-WRITE MASTER FIELDNAME=FILLER1, ALIAS=FILLER1, USAGE=A1, ACTUAL=A01, $
-WRITE MASTER FIELDNAME=FCST_SALES_AMT, ALIAS=FCST_SALES_AMT, USAGE=I6SC, ACTUAL=A06, $
-CLOSE
-RUN
-* FILEDEF
FILEDEF CLASS_SALES DISK class_sales_data.txt
-RUN
-* REPORT
TABLE FILE CLASS_SALES
HEADING
"CLASS_SALES"
PRINT
CLASS_ID
FCST_SALES_AMT
END
-RUN
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE +++ CREATE FILE - MONTH +++
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-* CREATE DATA
FILEDEF MONTH DISK month_data.txt
-RUN
-WRITE MONTH 201001
-WRITE MONTH 201002
-WRITE MONTH 201003
-WRITE MONTH 201004
-WRITE MONTH 201005
-WRITE MONTH 201006
-WRITE MONTH 201007
-WRITE MONTH 201008
-WRITE MONTH 201009
-WRITE MONTH 201010
-WRITE MONTH 201011
-WRITE MONTH 201012
-CLOSE
-RUN
-* CREATE MFD
FILEDEF MASTER DISK month.mas
-RUN
-WRITE MASTER FILE=MONTH, SUFFIX=FIX, $
-WRITE MASTER SEGNAME=MONTH, SEGTYPE=S0, $
-WRITE MASTER FIELDNAME=MONTH_ID, ALIAS=MONTH_ID, USAGE=A06, ACTUAL=A06, $
-CLOSE
-RUN
-* FILEDEF
FILEDEF MONTH DISK month_data.txt
-RUN
-* REPORT
TABLE FILE MONTH
HEADING
"MONTH"
PRINT
MONTH_ID
END
-RUN
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-TYPE +++ JOIN TABLES BY NEW COMMON "DUMMY_KEY" +++
-TYPE +++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-* HOLD CLASS_SALES(HLD_B)
DEFINE FILE CLASS_SALES
DUMMY_KEY/A1 = 'C';
END
TABLE FILE CLASS_SALES
PRINT
CLASS_ID
FCST_SALES_AMT
DUMMY_KEY
ON TABLE HOLD AS HLD_B
END
-* HOLD MONTH(HLD_A)
DEFINE FILE MONTH
DUMMY_KEY/A1 = 'C';
END
TABLE FILE MONTH
PRINT
MONTH_ID
BY DUMMY_KEY
ON TABLE HOLD AS HLD_A FORMAT FOCUS INDEX DUMMY_KEY
END
-* JOIN CLASS_SALES(HLD_B) TO MONTH(HLD_A) AND REPORT
JOIN DUMMY_KEY IN HLD_B TO ALL DUMMY_KEY IN HLD_A AS J01
TABLE FILE HLD_B
PRINT MONTH_ID CLASS_ID FCST_SALES_AMT
END

Jim


WebFocus 8.201M, Windows, App Studio
 
Posts: 227 | Location: Lincoln Nebraska | Registered: August 12, 2008Report This Post
Virtuoso
posted Hide Post
Nice Smiler
 
Posts: 1903 | Location: San Antonio | Registered: February 28, 2005Report This Post
Silver Member
posted Hide Post
[SOLVED]

Thanks all!


7.6.6
Windows 2003 Server, Sybase IQ, DB2, SQL Server and Oracle Databases
Excel, HTML and PDF
 
Posts: 34 | Registered: February 20, 2008Report This Post
Expert
posted Hide Post
Joel,

For information the result set that you required is called a "cartesian set" and the suggested method by JFR99 is known as Mcgyver (or MacGyver) technique and has many other uses. Use the search facility to discover others.

There are also many other useful sources in the Developer Centre such as the User Forum Presentations. To gain access to these just click on the bread crumb link for "IB - Developer Center" above and follow your nose.

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
  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] Populate next months with same information

Copyright © 1996-2020 Information Builders