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,

Do the two tables have anything in commmon...the one only has month?

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 ...

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

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,

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.

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

Nice

[SOLVED]

Thanks all!

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