Focal Point
About Table / Record question?

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

June 06, 2004, 03:05 PM
Ringo
About Table / Record question?
There are only those records in the table like this:

Year Month Value
-------------------------
2003 Jan 12345
2003 Feb 34534
2003 Mar 32324
2003 Apr 34522
2003 May 34554


My question is: How can I show the others Month in the report (eg. Jun, Jul, Aug, Sep, Oct, Nov & Dec)
June 07, 2004, 09:07 AM
<pranasH>
1. Create file with all mounths listed e.g. small FOCUS file
2. Use MERGE ... MATCH ... OLD-OR-NEW (check doc. for MERGE OLD-OR-NEW)

Pranas
June 07, 2004, 12:18 PM
Mikel
Or using FML (Financial Modeling Language):

TABLE FILE CAR
SUM SALES
FOR COUNTRY
'ENGLAND' OVER
'POLAND' OVER
'PORTUGAL' OVER
'SPAIN' OVER
'USA' OVER
'W GERMANY'
END

TABLE FILE
SUM VALUE
FOR MONTH
'Jan' OVER
'Feb' OVER
...
'Dec'
END

Regards,
Mikel

This message has been edited. Last edited by: <Mabel>,
June 07, 2004, 05:30 PM
susannah
Ringo, this is a classic use of the McGyver (or MacGyver) technique. its documented and written about. Do a search on the word in Knowledge Base (use both spellings Wink ) and then look up the article on it in TABLE FILE CAR. Its too long to explain here, but you'll figure it out, and its very famous part of focus lore, ibi should give out tshirts!
June 08, 2004, 03:04 AM
Ringo
Thanks, Mikel, it works, But can I put the Month at the ACROSS field.




TABLE FILE

SUM VALUE OVER
DISCOUNT
BY YEAR
ACROSS MONTH
FOR MONTH
'Jan' OVER
'Feb' OVER
...
'Dec'END

Please help me!

This message has been edited. Last edited by: <Mabel>,
June 08, 2004, 07:00 AM
Mikel
Yes. Yesterday I forgot to mention the use of ROWS and COLUMNS in order to define your own sort sequence:


-* Columns sort sequence.
TABLE FILE CAR
SUM SALES
BY CAR
ACROSS COUNTRY
COLUMNS
'ENGLAND' AND
'POLAND' AND
'PORTUGAL' AND
'SPAIN' AND
'USA' AND
'W GERMANY'
END
-RUN

-* Rows sort sequence.
TABLE FILE CAR
SUM SALES
BY CAR
BY COUNTRY
ROWS
'ENGLAND' OVER
'POLAND' OVER
'PORTUGAL' OVER
'SPAIN' OVER
'USA' OVER
'W GERMANY'
END
-RUN

egards,
Mikel

This message has been edited. Last edited by: <Mabel>,
June 08, 2004, 09:05 AM
Ringo
Thank you very much, Mikel, it works fine now!