Hi All.

Is it possible to read the values from one column into amper variables from a FOCUS hold file.

This hold file will be pivoted and I would like to know what the column names will be so I can re-order the columns in my report.

Many Thanks

DevStudion 7.6.1 SQL Server 2005

Fox

After your fex with the hold command you can type this

? HOLD

and you will see the fields and there format
that might help you a bit.

example

TABLE FILE CAR
SUM
     SALES AS ''
BY CAR
ACROSS COUNTRY
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT FOCUS
END
? HOLD

The five across fields give you five different fields all named with the country code.

You can now run a report based upon these fields

To put the contents of a hold file column into an amper variable, you need to create the hold file then do a -READ to populate the variables.

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-READ HOLD NOCLOSE &COUNTRY.10.


At this point, you can do whatever you need to with the amper variable.

Hope this helps.

Cheers. I shall give those ago.

If there are many records in the file, will the -READ command fill up an array?

i.e. In the country example there will be

&COUNTRY1 = 'England'
&COUNTRY2 = 'USA'....

Many Thanks

You can do that if you want. You need to use the Dialogue Manager index syntax. You can put the -READ in a -REPEAT loop and bump up a counter. Your code might look something like this:

TABLE FILE CAR
PRINT COUNTRY
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-SET &LIMIT=&LINES;
-SET &I=1;
-REPEAT ENDLOOP &LIMIT TIMES
-READ HOLD NOCLOSE &COUNTRY.10.
-SET &COUNTRY.&I=&COUNTRY;
-TYPE &COUNTRY.&I
-SET &I=&I+1;
-ENDLOOP

Thanks Ginny. That has worked a treat.

Don't suppose you know how to get rid of the white space that appears between the first row of data and the column headers on a pivot table?

quote:

This hold file will be pivoted and I would like to know what the column names will be so I can re-order the columns in my report.

To get the column names you can use the little used CHECK FILE syntax -

TABLE FILE CAR
SUM RCOST
    DCOST
 BY COUNTRY
 BY CAR
 BY MODEL
ON TABLE HOLD AS REPDATA
END
-RUN
CHECK FILE REPDATA HOLD
TABLE FILE HOLD 
PRINT FIELDNAME
ON TABLE SAVE AS COLNAMES
END
-RUN
-SET &Cnt = 0;
-READ COLNAMES &Column&Cnt.EVAL.A66.
-REPEAT :Loop WHILE &IORETURN EQ 0;
-SET &Cnt = &Cnt + 1;
-READ COLNAMES &Column&Cnt.EVAL.A66.
-:Loop
-? &Column

The HOLD file will have various columns within it containing various information about the individual fields (columns) and the three most commonly required are FIELDNAME, ALIAS and FORMAT. I have only pulled out the fieldname column and then read it into variables.

T

Foz,

No, I do not. I don't play with pivot tables very much.

Maybe someone else does and can post a solution for you.