Simple focexec query

Hi,
I have a flat file of data. looks something something like
a0001 1
a0002 2
a0003 3
a0001 2
a0002 3
a0003 1

All the 1's in col 10 , 2's col 11, 3's col 12

I want to run a focexec that produces
a0001 1 2
a0002 2 3
a0003 1 3

All fields defined SUFFIX=FIX in their relevant cols
i.e.
FILENAME=RACFGRP SUFFIX=FIX
SEGNAME=ROOT
FIELDNAME=USER, ALIAS=user, FORMAT=A8,
FIELDNAME=blk, ALIAS=b1, FORMAT=A1,
FIELDNAME=racf1, ALIAS=rac1, FORMAT=A1,
FIELDNAME=RACF2, ALIAS=rac2, FORMAT=A1,
FIELDNAME=RACF3, ALIAS=rac3, FORMAT=A1,

I cannot seem to get away from Focus producing:
a0001 1
2
a0002 2
3
a00003 1
3

Any help would be appreciated.
Thanks

Posts: 3 | Registered: October 11, 2007

Tony A

Expert

posted

Hide Post

I take it you mean that your data looks like this? -

a0001    1
a0002     2
a0003      3
a0001     2
a0002      3
a0003    1

Have you tried?

TABLE FILE RACFGRP
SUM MAX.RACF1
    MAX.RACF2
    MAX.RACF3
BY USER
END

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Show Post

GinnyJakes

Expert

posted

Hide Post

Building on Tony's example:

TABLE FILE RACFGRP
SUM MAX.RACF1
MAX.RACF2
MAX.RACF3
BY USER
ON TABLE HOLD FORMAT ALPHA
END
DEFINE FILE HOLD
DRACF1/A1=IF RACF1 EQ ' ' THEN RACF2 ELSE RACF1;
DRACF2/A1=IF RACF2 EQ RACF1 THEN RACF3 ELSE IF RACF2 EQ ' ' THEN RACF3 ELSE RACF2;
DRACF3/A1=IF RACF3 EQ RACF2 THEN ' ' ELSE IF RACF3 EQ ' ' THEN RACF3;
END
TABLE FILE HOLD
PRINT DRACF1 AS RACF1
DRACF2 AS RACF2
DRACF3 AS RACF3
BY USER
END

You might have to play with that 3rd DEFINE if one user had all 3.

Also, your master needs a little work:

FILENAME=RACFGRP,SUFFIX=FIX
SEGNAME=ROOT
FIELDNAME=USER, ALIAS=user, FORMAT=A5,ACTUAL=A5,$
FIELDNAME=blk, ALIAS=b1, FORMAT=A1,ACTUAL=A1,$
FIELDNAME=RACF1, ALIAS=rac1, FORMAT=A1,ACTUAL=A1,$
FIELDNAME=RACF2, ALIAS=rac2, FORMAT=A1,ACTUAL=A1,$
FIELDNAME=RACF3, ALIAS=rac3, FORMAT=A1,ACTUAL=A1,$

Don't forget the actuals. There was also a comma missing on the first line and some field names were in lower case.

Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google

Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006

Ignored post by GinnyJakes posted

Show Post

spydj1

Member

posted

Hide Post