I am new to WebFOCUS developer and definitely a novice. I have two format questions. I have gone through the forum but I am not sure the best action for my situation.

My format is currently in excel and is formatted to sort through one column (will say column “W”) over multiple spreadsheets.

___________________

Here is the current format at a certain date of time:
Date: 09/23/2012
Column W____________Column X___________________Column Y___________________Column Z
Alpha1______________$$$$$_____________________$$$$$_______________________$$$$
Alpha2______________$$$$$_____________________$$$$$_______________________$$$$

What I want is:
Column W____________09/23/2011____________09/23/2012
Alpha1
X___________________$$$$$_________________$$$$
Y___________________$$$$$_________________$$$$
Z___________________$$$$$_________________$$$$
Alpha2
X___________________$$$$$_________________$$$$
Y___________________$$$$$_________________$$$$
Z___________________$$$$$_________________$$$$

I currently have fields (XYZ) that are currently going vertically and I want them to be repeating going across horizontally after each field change in column W. I would also like the values for fields XYZ to be shown at two different dates of times.

What would be the best way to write this format?
Thanks in advance!

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

Look for OVER command in the documentation.

A more accurate way of addressing the problem is to use the McGyver technique.

Your example is provide below:

 APP FI desc DISK data.mas
APP FI data DISK data.ftm
-RUN

-* Create data - in your case your Excel file
-*          Date_______W______X_____ Y_____ Z_____
-WRITE data 09/23/2012 Alpha1 1x$$$  1y$$$  1z$$$
-WRITE data 09/23/2012 Alpha2 2x$$$  2y$$$  2z$$$
-WRITE data 09/24/2012 Alpha1 3x$$$  3y$$$  3z$$$
-WRITE data 09/24/2012 Alpha2 4x$$$  4y$$$  4z$$$
-WRITE data 09/25/2012 Alpha1 5x$$$  5y$$$  5z$$$
-WRITE data 09/25/2012 Alpha2 6x$$$  6y$$$  6z$$$

-* Create synonym - in your case, Excel
-WRITE desc FILE=data,SUFFIX=FIX
-WRITE desc FIELD=date,FORMAT=A10,ACTUAL=A10,$
-WRITE desc FIELD=w,FORMAT=A7,ACTUAL=A7,$
-WRITE desc FIELD=x,FORMAT=A7,ACTUAL=A7,$
-WRITE desc FIELD=y,FORMAT=A7,ACTUAL=A7,$
-WRITE desc FIELD=z,FORMAT=A7,ACTUAL=A7,$

-*  McGyver Technique setup...

-*    First set up the master file.  Note that the top line of the master MUST
-*    be a full line of 80 spaces.  (This is acheived by LRECL below)
-*    Do NOT delete that line or the technique will not work.

APP FI mcgyver DISK mcgyver.mas (LRECL 80
-RUN

-WRITE mcgyver
-WRITE mcgyver FILE=MCGYVER,SUFFIX=FIX
-WRITE mcgyver SEGNAME=SEG1
-WRITE mcgyver FIELD=blank,,A1,A1,$
-WRITE mcgyver SEGNAME=SEG2,OCCURS=80,PARENT=SEG1
-WRITE mcgyver FIELD=1char,,A1,A1,$
-WRITE mcgyver FIELD=counter,ORDER,I4,I4,$

-*    Set up the define based join.  We join ANY field in the source file
-*    to the MCGYVER master so that we replicate each row in the source file
-*    80 times. If you need to replicate more than 80 times, you need a
-*    populated data file of the width specified in the OCCURS parameter above.

JOIN blank WITH date IN data TO blank IN mcgyver  AS MC

DEFINE FILE data
blank/A1 WITH date = ' ';
value/A7 = IF counter EQ 1 THEN x ELSE
           IF counter EQ 2 THEN y ELSE z;
END

TABLE FILE data
SUM value
ACROSS date
BY w NOPRINT SUBHEAD
"<w"
BY counter
IF counter LT 4
END 

Put this in a FEX and execute it to see how it works.

Candice,

Another solution is to use an alternate master file:

  
-* File candice01.fex
DEFINE FILE CAR
CDATE/DMYY=SEATS + 40600;
NSALES/D7=SALES;
ARCOST/A12='RETAIL COST';
ADCOST/A12='DEALER COST';
ASALES/A12='SALES';
END
-*
TABLE FILE CAR
SUM ARCOST RCOST ADCOST DCOST ASALES NSALES
BY CDATE BY COUNTRY
ON TABLE SAVE AS CANDICE
END
-RUN
-GOTO #NXT

The EDAPUT command syntax is:
EX -LINES {n} EDAPUT {File Type},{App/}{File Name},{Create Type},{Create Location}

This allows you to create a temporary MASTER in memory.

n                       : Number of lines including EDAPUT Line
File Type               : Type of File (e.g. MASTER, FOCEXEC, ACCESS, etc )
App/                    : Optionally Specify the APP directory
File Name               : Name of the File without the extension
Create Type             : Type of Create (CV=Create Variable, C=Create Fixed, A=Append to file)
Create Location         : File Location (FILE=Write to Current Location, MEM=Write to Memory only)
-#NXT
-*
EX -LINES 8 EDAPUT MASTER,CANDICE,C,MEM 
 FILENAME=CANDICE    , SUFFIX=FIX
 SEGMENT=CANDICE, SEGTYPE=S0
 FIELDNAME=CDATE, ALIAS=E01, USAGE=DMYY, ACTUAL=A08, $
 FIELDNAME=COUNTRY, ALIAS=E02, USAGE=A10, ACTUAL=A10, $
 SEGMENT=COSTS, PARENT=CANDICE, OCCURS=VARIABLE
 FIELDNAME=CNAME, ALIAS=E03, USAGE=A12, ACTUAL=A12, $
 FIELDNAME=COST, ALIAS=E04, USAGE=D7, ACTUAL=A07, $
-RUN
-*
-GOTO NXT2
Explanations:
This MASTER looks at the CANDICE file as a hierarchy, where the root segment is made of the date and "column W". The child segment is a repetition 2 columns. The first is the name of the columns X,Y,Z; the second the values of X, Y and Z. 

-NXT2
TABLE FILE CANDICE
SUM COST
ACROSS CDATE
BY COUNTRY NOPRINT
BY CNAME
ON COUNTRY SUBHEAD
"<COUNTRY "
END