I have 3 field in input file as follows;-

input;-
roll no. des no. des
21 1 aaaaa
22 1 bbbbb
22 2 ccccc
23 1 ddddd
23 2 eeeee
23 3 fffff
24 1 ggggg

i want to have in output three 'des' fields as des1 des2 des3 corresponding to des no.as shown

output;-
roll no. des1 des2 des3
21 aaaaa
22 bbbbb ccccc
23 ddddd eeeee fffff
24 ggggg

can anyone help me figure it out?

Use across and look at the manual

have you tried

TABLE FILE fn
BY ROLL_NO
ACROSS DES
END

Not knowing exactly what you're going for, here is a really pain in the neck code wise way to get information across when the field has different values in a table:
Note the table request uses the SUM verb, did this as if didn't a field would show as blanks if you just did a simple define. If the field is numeric, use FST. prefix.

PPHONE/A20 = IF STU_ID NE LAST STU_ID THEN ' ';
LPHONE/A20 = IF STU_ID NE LAST STU_ID THEN ' ';
UNOEMAIL/A50= IF AD113 EQ 'U' THEN AD110
ELSE IF AD123 EQ 'U' THEN AD120
ELSE IF AD133 EQ 'U' THEN AD130
ELSE IF AD143 EQ 'U' THEN AD140
ELSE IF AD153 EQ 'U' THEN AD150
ELSE UNOEMAIL;
PERSONLEMAIL/A50 = IF STU_ID NE LAST STU_ID THEN ' ';
PERSONLEMAIL/A50= IF AD113 EQ 'P' THEN AD110
ELSE IF AD123 EQ 'P' THEN AD120
ELSE IF AD133 EQ 'P' THEN AD130
ELSE IF AD143 EQ 'P' THEN AD140
ELSE IF AD153 EQ 'P' THEN AD150
ELSE PERSONLEMAIL ;
WKPHONE/A20 = IF STU_ID NE LAST STU_ID THEN ' ';
WKPHONE/A20= IF AD013 EQ 'B' THEN AD010
ELSE IF AD023 EQ 'B' THEN AD020
ELSE IF AD033 EQ 'B' THEN AD030
ELSE IF AD043 EQ 'B' THEN AD040
ELSE IF AD053 EQ 'B' THEN AD050
ELSE WKPHONE ;
LSTREET1/A32 = IF STU_ID NE LAST STU_ID THEN ' ';
LSTREET2/A32 = IF STU_ID NE LAST STU_ID THEN ' ';
LCITY/A20 = IF STU_ID NE LAST STU_ID THEN ' ';
LZIP/A15 = IF STU_ID NE LAST STU_ID THEN ' ';
LSTATE/A2 = IF STU_ID NE LAST STU_ID THEN ' ';
LCOUNTRY/A2 = IF STU_ID NE LAST STU_ID THEN ' ';
PSTREET1/A32 = IF STU_ID NE LAST STU_ID THEN ' ';
PSTREET2/A32 = IF STU_ID NE LAST STU_ID THEN ' ';
PCITY/A20 = IF STU_ID NE LAST STU_ID THEN ' ';
PZIP/A15 = IF STU_ID NE LAST STU_ID THEN ' ';
PSTATE/A2 = IF STU_ID NE LAST STU_ID THEN ' ';
PCOUNTRY/A2 = IF STU_ID NE LAST STU_ID THEN ' ';
LSTREET1/A32 = IF AD215 EQ 'L' OR AD220 EQ 'L' OR AD225 EQ 'L'
OR AD230 EQ 'L' OR AD235 EQ 'L' THEN AD240 ELSE LSTREET1;
LSTREET2/A32 = IF AD215 EQ 'L' OR AD220 EQ 'L' OR AD225 EQ 'L'
OR AD230 EQ 'L' OR AD235 EQ 'L' THEN AD245 ELSE LSTREET2;
LCITY/A20 = IF AD215 EQ 'L' OR AD220 EQ 'L' OR AD225 EQ 'L'
OR AD230 EQ 'L' OR AD235 EQ 'L' THEN AD250 ELSE LCITY;
LSTATE/A2 = IF AD215 EQ 'L' OR AD220 EQ 'L' OR AD225 EQ 'L'
OR AD230 EQ 'L' OR AD235 EQ 'L' THEN AD255 ELSE LSTATE;
LZIP/A15= IF AD215 EQ 'L' OR AD220 EQ 'L' OR AD225 EQ 'L'
OR AD230 EQ 'L' OR AD235 EQ 'L' THEN AD260 ELSE LZIP;
LCOUNTRY/A2= IF AD215 EQ 'L' OR AD220 EQ 'L' OR AD225 EQ 'L'
OR AD230 EQ 'L' OR AD235 EQ 'L' THEN AD263 ELSE LCOUNTRY;
PSTREET1/A32 = IF AD215 EQ 'P' OR AD220 EQ 'P' OR AD225 EQ 'P'
OR AD230 EQ 'L' OR AD235 EQ 'P' THEN AD240 ELSE PSTREET1;
PSTREET2/A32 = IF AD215 EQ 'P' OR AD220 EQ 'P' OR AD225 EQ 'P'
OR AD230 EQ 'P' OR AD230 EQ 'P' THEN AD245 ELSE PSTREET2;
PCITY/A20 = IF AD215 EQ 'P' OR AD220 EQ 'P' OR AD225 EQ 'P'
OR AD230 EQ 'P' OR AD235 EQ 'P' THEN AD250 ELSE PCITY;
PSTATE/A2 = IF AD215 EQ 'P' OR AD220 EQ 'P' OR AD225 EQ 'P'
OR AD230 EQ 'P' OR AD230 EQ 'P' THEN AD255 ELSE PSTATE;
PZIP/A15= IF AD215 EQ 'P' OR AD220 EQ 'P' OR AD225 EQ 'P'
OR AD230 EQ 'P' OR AD235 EQ 'P' THEN AD260 ELSE PZIP;
PCOUNTRY/A2= IF AD215 EQ 'P' OR AD220 EQ 'P' OR AD225 EQ 'P'
OR AD230 EQ 'P' OR AD235 EQ 'P' THEN AD263 ELSE PCOUNTRY;
PPHONE/A20 = IF AD215 EQ 'P' OR AD220 EQ 'P' OR AD225 EQ 'P'
OR AD230 EQ 'P' OR AD235 EQ 'P' THEN AD265 ELSE PPHONE;
LPHONE/A20 = IF AD215 EQ 'L' OR AD220 EQ 'L' OR AD225 EQ 'L'
OR AD230 EQ 'L' OR AD235 EQ 'L' THEN AD265 ELSE LPHONE;
CUM_GPA/P7.3 = IF STU_ID EQ LAST STU_ID THEN 0 ELSE RA380;
HE_GPA/P7.3 = IF STU_ID EQ LAST STU_ID THEN 0 ELSE RA395;
CURHOURS/P8.2 = IF STU_ID EQ LAST STU_ID THEN 0 ELSE RT020;
CUMHRS/P8.2 = IF STU_ID EQ LAST STU_ID THEN 0 ELSE RA320;
COLLEGE/A3 = IF STU_ID EQ LAST STU_ID THEN ' ' ELSE RT105_RTPG;
MAJOR1/A4 = IF STU_ID EQ LAST STU_ID THEN ' ' ELSE RT120_RTPG;
MAJOR2/A4 = IF STU_ID EQ LAST STU_ID THEN ' ' ELSE RT125;
CAREER/A2 = IF STU_ID EQ LAST STU_ID THEN ' ' ELSE RA305_RACS
END
TABLE FILE AMINTBL_UNO_PROD
SUM
SIDFORM AS 'Stu ID'
SEVIS_ID AA003 AA011 AA013 AA010 AMD31 AA023 AMD21 AMD22 AMD23
AMD24 AMD25 AMD26 AMD29
LSTREET1 AS 'Local Address 1'
LSTREET2 AS 'Local Address 2'
LCITY AS 'Local City'
LSTATE AS 'Local State'
LZIP AS 'Local ZIP'
LCOUNTRY AS 'Local Country'
LPHONE AS 'Local Phone'
PSTREET1 AS 'Permanent Address 1'
PSTREET2 AS 'Permanent Address 2'
PCITY AS 'Permanent City'
PSTATE AS 'Permanent State'
PZIP AS 'Permanent ZIP'
PCOUNTRY AS 'Permanent Country'
PPHONE AS 'Permanent Phone'

Leah at UNO

this code will not work....

I would suggest to start with a
DEFINE FILE AMINTBL_UNO_PROD

and maybe also an END somewhere.

FrankDutch, thanks for the comment, but I just didn't cut and paste the define part of the code, just a segment of it. Should remember to do that next time to show for those not really familiar with the FOCUS language. I've done the same thing with compute statements as well.

hey guys
I tried to do it using both by and across but it did'nt work .
I must tell u that i also have other fields to be printed along with these fields and moreover this time i
dont have the des no. field only the roll no and des field.
To explain the problem in more descriptive manner.....
I have some 5 field to be printed corresponding to a particular roll no.
Now for a particular roll no. i have multiple records for des as i had shown in the example.
Now i need to print a single record for each roll no. which contain all the other fields and the multiple des fields
which were coming in different rows before, I want it in single row but in different columns
and the other fields to be printed as normal.
Any suggestions

In a simplistic approach try:

DEFINE FILE ...
CNTR/I4 = IF ROLL_NO NE LAST ROLL_NO THEN 1 ELSE CNTR + 1;
FNAME/A8 = 'DES' ! EDIT(CNTR);
END
TABLE FILE ...
WRITE DES
ACROSS FNAME AS ''
BY ROLL_NO
END
(the ! is the concatenation symbol which I can't find on this portuguese Mac keyboard)

Hope this helps.

hey Alan i tried it but its printing only 1 column of des0001 but i want the same no of columns as the no. of records for each roll_no.
i.e. i also want it to des0002 and des0003 columns for each roll_no

From a focus test file I created that tables directly as:

 
ROLL_NO DES_NO DES
21 1 AAAAAA
22 1 BBBBBB
22 2 CCCCCC
23 1 DDDDDD
23 2 EEEEEE
23 3 FFFFFF
24 1 GGGGGG

Using my code, I get:

 
ROLL-NO DES0001 DES0002 DE0003
21 AAAAAA
22 BBBBBB CCCCCC
23 DDDDDD EEEEEE FFFFFF
24 GGGGGG

Which I figure is correct.

The ACROSS gives the DES0001 DES0002 DES00003. If you change the ACROS to a BY you should get:

FNAME ROLL_NO DES
DES0001 21 AAAAAA
        22 BBBBBB
        23 DDDDDD
        24 GGGGGG
DES0002 22 CCCCCC
        23 EEEEEE
DES0003 23 FFFFFF

Can you let me know what you are getting here and your code.

HI ALAN,

WHEN I USE

TABLE FILE H
PRINT ROLL DES
BY ROLL NOPRINT
END

I GET THE FOLLOWING OUTPUT

roll des
----- ---
746 INS
222 BUYR
610 INSP
610 FIN
325 BUY IN
325 BUY FIN
279 SEP
279 NAN
529 PECT
121 TION
121 CING

BUT I DONT WANT IT IN THIS WAY
I WANT IT AS:-

ROLL DES1 DES2 DES3
---- ------ ---- ---
746 INS
222 BUYR
610 INSP FIN
325 IN FINS
279 SEP NAN
529 PECT
121 TION CING



MY CODE IS AS FOLLOWS:-

DEFINE FILE H
CNTR/I4=IF ROLL NE LAST ROLL THEN 1 ELSE CNTR + 1;
FNAME/A8='DES'|EDIT(CNTR);
END
TABLE FILE H
WRITE DES
ACROSS FNAME
BY ROLL
END

THE OUTPUT WHICH I AM GETTING IS:-


FNAME
ROLL DES0001

----- -----------------------
746 INS
222 BUYR
610 FIN
325 FINS
279 NAN
529 PECT
121 CING

DES0002 AND DES003 ARE NOT PRINTED. FOR ROLL 610 325 279 AND 121 IN DES001
IT IS PRINTING THE VALUES WHICH SHOULD BE IN DES0002.
MOREOVER I TRIED IT USING 'BY' INSTEAD OF 'ACROSS' THIS IS THE OUTPUT I GOT



FNAME ROLL DES
----- ---- ------------
DES0001 746 INS
222 BUYR
610 FIN
325 FINS
279 NAN
529 PECT
121 CING

CORRECT ME IF I AM DOING SUMTHING WRONG

THANKS.

HI ALAN,
SORRY LET ME CORRECT MY 1ST OUTPUT WHICH I GOT
THIS ID THE CORRECTED ONE.
WHEN I USE

TABLE FILE H
PRINT ROLL DES
BY ROLL NOPRINT
END

I GET THE FOLLOWING OUTPUT

roll des
----- ---
746 INS
222 BUYR
610 INSP
610 FIN
325 IN
325 FINS
279 SEP
279 NAN
529 PECT
121 TION
121 CING

BUT I DONT WANT IT IN THIS WAY
I WANT IT AS:-

ROLL DES1 DES2 DES3
---- ------ ---- ---
746 INS
222 BUYR
610 INSP FIN
325 IN FINS
279 SEP NAN
529 PECT
121 TION CING



MY CODE IS AS FOLLOWS:-

DEFINE FILE H
CNTR/I4=IF ROLL NE LAST ROLL THEN 1 ELSE CNTR + 1;
FNAME/A8='DES'|EDIT(CNTR);
END
TABLE FILE H
WRITE DES
ACROSS FNAME
BY ROLL
END

THE OUTPUT WHICH I AM GETTING IS:-


FNAME
ROLL DES0001

----- -----------------------
746 INS
222 BUYR
610 FIN
325 FINS
279 NAN
529 PECT
121 CING

DES0002 AND DES003 ARE NOT PRINTED. FOR ROLL 610 325 279 AND 121 IN DES001
IT IS PRINTING THE VALUES WHICH SHOULD BE IN DES0002.
MOREOVER I TRIED IT USING 'BY' INSTEAD OF 'ACROSS' THIS IS THE OUTPUT I GOT



FNAME ROLL DES
----- ---- ------------
DES0001 746 INS
222 BUYR
610 FIN
325 FINS
279 NAN
529 PECT
121 CING

CORRECT ME IF I AM DOING SUMTHING WRONG

THANKS.

Ah. What file system are you using Manash?

I was relying on a focus file keyed on roll_no and des_no.

Add a step at the beginning to get the roll_no and des_no into a sequence:

TABLE FILE H
PRINT DES
BY ROLL_NO
BY DES_NO
ON TABLE HOLD
END

Then carry on as before using the hold file.

DEFINE FILE HOLD
CNTR/I4 = IF ROLL_NO NE LAST ROLL_NO THEN 1 ELSE CNTR + 1;
FNAME/A8 = 'DES' | EDIT(CNTR);
END
TABLE FILE HOLD
WRITE DES
ACROSS FNAME AS ''
BY ROLL_NO
END

The define using the LAST object, rely's on the data coming in ordered.

Let me know.
(Found concat symbol. It is,of course, the carat key. Now where is the carat key?)

This message has been edited. Last edited by: Alan B,

HI ALAN
IT WORED AT LAST.
THANX ANYWAYS
I WAS USING FLAT FILES FOR BOTH INPUT AS WELL AS OUTPUT.
THANX

Hi Manash,
This is maybe a bit late but you might be able to use it in the future:
-* number the DES for each ROLL
TABLE FILE H
LIST DES
BY ROLL
ON TABLE HOLD
END
-* find the greatest number of occurences of DES per ROLL
TABLE FILE HOLD
WRITE MAX.LIST
ON TABLE SAVE
END
-RUN
-READ SAVE,&MAXLIST
-* generate columns for DES1 DES2 ...
DEFINE FILE HOLD
DESD/A6=DECODE LIST(
-REPEAT #GEN FOR &I FROM 1 TO &MAXLIST;
&I DES&I
-#GEN
);
END
TABLE FILE HOLD
WRITE DES
BY ROLL
ACROSS DESD AS ''
END

Thanx Danny for your suggestions. Its never too late to learn.