I need not display the ID_value,ID_TYPE and also ID.
Hence for a previous report , i had requirement wherein i need to convert only two rows as two columns.Hence i used the hold file concept , where in i created two separate hold file with FORMAT FOCUS INDEX syntax and joined both the hold files. But in this case i need to do this for atleast 12 to 14 rows. Is there any other way to do it ?
I had tried the below(EXAMPLE FOR TWO ROWS ALONE) :
TABLE FILE SQLOUT
PRINT
*
ON TABLE HOLD AS HLD_NCP
END
DEFINE FILE HLD_NCP
ID
State_License/A50= IF ID_TYPE_C EQ 2454 THEN PHARM_DEF_LOC_ID_I ELSE ' ';
NPI/A50 = IF ID_TYPE_C EQ 2451 THEN PHARM_DEF_LOC_ID_I ELSE ' ';
END
TABLE FILE HLD_NCP
SUM
PHARM_DEF_LOC_I
State_License
NPI
BY PHARM_DEF_LOC_I NOPRINT
END
Output is as follows: ------------------------------------- PAGE 1
PHARM_DEF_LOC_I State_License NPI AAA
Here Statelicense and NPI are blank.
When you change the SUM to PRINT , output is as follows:
Ginny has the right approach. Just beware that ACROSS is limited to 64 values. Now, when you sort ACROSS, you want to number the occurences for each ID. WF provides a nice feature: LIST.
TABLE FILE CAR
LIST SALES SEATS
BY COUNTRY
ON TABLE HOLD
END
-RUN
DEFINE FILE HOLD
ALIST/A5=LJUST(5, FTOA(LIST, '(D5c)', 'A5'), 'A5');
COLS/A12='COLUMN' || ALIST;
END
TABLE FILE HOLD
SUM SALES SEATS
BY COUNTRY
ACROSS COLS AS ''
ON TABLE SET HTMLCSS ON
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
August 20, 2009, 01:40 PM
GamP
Jan, Danny,
The limit for ACROSS columns and values have been greatly increased. In 711 it was a maximum of 1024, later releases should support a total of 1056. This is all limited further by the maximum record length overall, which is 32 Kb.
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
August 24, 2009, 07:47 AM
Jan1
Hi,
Using ACROSS i was able to resolve the issue,like below:
DEFINE FILE PHMSTR
State_License/I2= IF PHARM_DEF_LOC_I EQ LAST PHARM_DEF_LOC_I THEN State_License + 1 ELSE 1 ;
END
TABLE FILE PHMSTR
SUM
PHARM_DEF_LOC_ID_I
ACROSS State_License AS ''
BY Store