I have a (HOLD) table with the following data:

CODE YR QTR AMT
A 2007 1 100
A 2007 2 200
A 2007 4 225
A 2008 1 100
A 2008 2 200
B 2007 1 100
B 2007 2 200
B 2007 3 225
B 2007 4 100
B 2008 1 200

But I need to fill in some 'missing data' within the periods of 2007 1st Quarter and 2008 2nd Quarter
The "Missing Data" is:
Code A Year 2007 Quarter 3 (Amt = 0)
Code B Year 2008 Quarter 2 (Amt = 0)


I'd like the final (HOLD) table to be:

CODE YR QTR AMT
A 2007 1 100
A 2007 2 200
A 2007 3 0
A 2007 4 225
A 2008 1 100
A 2008 2 200
B 2007 1 100
B 2007 2 200
B 2007 3 225
B 2007 4 100
B 2008 1 200
B 2008 2 0


I guess I need to use DM with repeat and writes? Any suggestions?

IIRC I have used the McGuyver technique in such a situation.

Use ROWS...OVER...

TABLE FILE WHATEVER
SUM AMT
BY CODE
BY YEAR
BY QTR ROWS 1 OVER 2 OVER 3 OVER 4
END

The 'ROWS...OVER" will not work since the quarters will vary - and I still have more processing to do on the hold file. (The McGyver may work but I think there is still a better solution - but I don't know it.)

I over-simplified what I needed so let me try to explain my problem once again.

I am running a report for 2nd quarter 2008

For Year 2007 I need data for all 6 quarters passed - from 1st quarter 2007 thru 2nd quarter 2008

For Year 2008 I need data for just 2 quarters passed - from 1st quarter 2008 to 2nd quarter 2008

Let say for example I have the following data in a hold file

TABLE HOLDA

YR QTR PASSED AMT
--- --------- ----
2007 1 $100
2007 2 $200
2007 3 $225
2007 5 $400
2008 1 $200

I need to fill in the missing data as shown below

TABLE HOLDB

YR QTR PASSED AMT
--- --------- ----
2007 1 $100
2007 2 $200
2007 3 $225
2007 4 $0 <--new row
2007 5 $400
2007 6 $0 <--new row
2008 1 $200
2008 2 $0 <--new row

How can I get to Table HOLDB with only the data in HOLDA

I think Mickey is on the right track.

Do you know how many quaters are expected per year ?

I think if you use ROWS 1 through 6, then on the next TABLE FILE, throw away the ones you don't want, you will get what you want.

If you have a predicatble, unchanging, range of quarter values then using OVER as Mickey suggests is the method to use.

However, if you do not know your range then using a method similar to McGyver (but not quite the same) would be the better method. McGyver uses a "loose" join scenario to create data "out of thin air" but that is not exactly what you want. You need to be able to build your data values and use them as the parent file combined via a LEFT OUTER join to your actual data file (even if that does give you a one to many join ).

I use a similar technique to force a complete range of dates into a calendar type fex where only days of absence exist and the end user requires a complete date range. You could easily adapt the code (see below) to account for quarter values by using the appropriate WF date function.

-* The &Year is defaulted here but can easily be supplied via user input.
-DEFAULT &Year  = 2006
FILEDEF DATEMAS DISK DATERNG.MAS
-RUN
-WRITE DATEMAS FILE=DATERNG,SUFFIX=XFOC
-WRITE DATEMAS SEGNAME=SEG1
-WRITE DATEMAS FIELD=DATE_KEY, ,DMYY ,DMYY , $
-RUN
-* Now create it
CREATE FILE DATERNG
-* and add all possible dates within a year
MODIFY FILE DATERNG
FREEFORM DATE_KEY.A8.
LOG FORMAT MSG OFF
LOG TRANS MSG OFF
LOG INVALID MSG OFF
DATA
-* This repeat loop will ensure at least a whole year is input,
-* NOTE: Because the field is an internal dat, any invalid dates will be rejected.
-SET &Day   = 1;
-SET &Month = 1;
-SET &Yearx = &Year;
-REPEAT :Loop1 2 TIMES;
-REPEAT :Loop2 12 TIMES;
-REPEAT :Loop3 31 TIMES;
-SET &Date = IF &Day LT 10 THEN '0' || &Day ELSE &Day;
-SET &Mnth = IF &Month  LT 10 THEN '0' || &Month ELSE &Month;
&Date&Mnth&Yearx
-SET &Day = &Day + 1;
-:Loop3
-SET &Day = 1;
-SET &Month = &Month + 1;
-:Loop2
-SET &Day = 1;
-SET &Month = 1;
-SET &Yearx = &Yearx + 1;
-:Loop1
END
-RUN

T

Jammer,
I am a stubborn proponent of the McGuyver technique.

JAMMER master:

  
FILE=JAMMER, SUFFIX=FIX,
   DATASET=C:\IBI\APPS\FOCALPOINT\JAMMER.TXT
 SEGNAME=SEG1, SEGTYPE=S0
 FIELD=CODE, ALIAS=CD, USAGE=A1, ACTUAL=A1, $
 FIELD=YR, ALIAS=YR, USAGE=YY, ACTUAL=A4, $
 FIELD=QTR, ALIAS=QTR, USAGE=I1, ACTUAL=A1, $
 FIELD=AMT, ALIAS=AMT, USAGE=I5C, ACTUAL=A3, $

JAMMER data:

  
A20071100
A20072200
A20074225
A20081100
A20082200
B20071100
B20072200
B20073225
B20074100
B20081200

JAMMER procedure:

  
-* File JAMMER2.fex
-SET &ECHO=ALL;
-*
-* from-to limits
-DEFAULT &FRQTR='2007 Q1', &TOQTR='2008 Q4'
-*
SET HOLDLIST=PRINTONLY
-*
-* Join your file to McGuyver
JOIN CLEAR *
JOIN BLANK WITH QTR IN JAMMER TO BLANK IN FSEQ AS W_
-*
-* Define a FOCUS date for Quarter
DEFINE FILE JAMMER
BLANK/A1 WITH QTR = ' ';
YRQ/YYQ=YR;
YRQTR/YYQ=YRQ + QTR -1;
B_YRQTR/YYQ='&FRQTR';
E_YRQTR/YYQ='&TOQTR';
-*
-* the running quarter to get all quaterly dates
ALLYRQTR/YYQ=B_YRQTR + COUNTER -1;
END
-*
-* generate all quarters
TABLE FILE JAMMER
BY CODE
BY ALLYRQTR
WHERE ALLYRQTR GE B_YRQTR
WHERE ALLYRQTR LE E_YRQTR
ON TABLE HOLD AS ALLQRTRS
END
-*
-* match all quarters to existing ones
MATCH FILE JAMMER
SUM
AMT
BY CODE 
BY YRQTR
RUN
FILE ALLQRTRS
BY CODE
BY ALLYRQTR AS YRQTR
AFTER MATCH HOLD NEW
END
-*
-* show output
TABLE FILE HOLD
PRINT AMT
BY CODE
BY YRQTR
END

Should do the trick.

Thank you all for your suggestions - I think each one would work.

I tried Mickey's suggestion with the 'ROWS OVER'
and as Waz said - throwing out the ones I don't want. (This seemed like the easiest solution). It seems to be working!