Hi,

I have a report with this format :-

Col1    Col2    Col3   Col4     Col5 Col6
A       T        E1     Paper    V1    10
B       Z        F1     Metals   V2    20
B       Z        F1     Metals   V3    20
C       K        D1     Food     V4   100

but the requirement is to display the report in this format :-

Col1    Col2    Col3   Col4     Col5  Col6
A        T       E1    Paper    V1     10
B        Z       F1    Metals   V2     20
		              V3     
C        K       D1    Food     V4    100

Please advise on how this can be done. Row 3 data is same as row 2 except for one field Col5. This datset returned from the database could have any number of rows. Output format is EXL2K.

I have tried searching the documentation and focal point posts, but couldn't find any.

Thank you.

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

TABLE FILE filename
PRINT COL6
BY COL1
BY COL2
BY COL3
BY COL4
BY COL5
ON TABLE PCHOLD FORMAT EXL2K
END

Do you have SET BYDISPLAY=ON in the program because that will cause the report to display in the first format you showed in your post.

If the data is in the correct order, you could DEFINE the report columns using LAST (the previous row to the current row), e.g.:

DEFINE FILE XXX
COL1X/A1 = IF COL1 EQ LAST COL1 THEN '' ELSE COL1;
COL2X/A1 = IF COL2 EQ LAST COL2 THEN '' ELSE COL2;
...
END

TABLE FILE XXX
PRINT 
COL1X AS 'COL1'
COL2X AS 'COL2'
...
BY COL1 NOPRINT
BY COL2 NOPRINT
...
END

Documentation: Using Functions > Data Source and Decoding Functions > LAST: Retrieving the Preceding Value

Thank you for your suggestion Ginny, I tried that and set bydispplay=off, but its not giving me the correct result. Here is the code

TABLE FILE HOLDEU

PRINT 
COMPUTE T/F8C = YTD_SALES/BUSINESS_PLAN_AMOUNT * 100; AS ''
COMMENTS AS ''
BY HIGHEST 10 BUSINESS_PLAN_AMOUNT NOPRINT
BY EUNAME
BY SALES_REP_NAME AS ''
BY REGDESC AS ''
BY CUSTOMER_NAME AS ''
BY MARKET_SEGMENT_DESC AS ''
BY BUSINESS_PLAN_AMOUNT  AS ''
BY YTD_SALES  AS ''

HEADING
"End User<+0>Terr/Mgr<+0>Region/District<+0>Segment<+0>Channel<+0>BP$<+0>FYTD$<+0>Tracking(%)<+0> <+0>Comments/Status/Issues"
-*"Top 10 End User Report"
  

-- Resulted Output

Col1    Col2    Col3   Col4     Col5  Col6
A        T       E1    Paper    V1     10
B        Z       F1    Metals   V2     20
		              V3  20   
C        K       D1    Food     V4    100

The columns after Col5 are repeating. Col 5 in the code is Customer_Name.

Thank you.
KK

KK, is this perhaps what you need ?

DEFINE FILE TESTDATA
COL1_N/A1  MISSING ON = IF COL1 NE LAST COL1 THEN COL1 ELSE MISSING;
COL2_N/A1  MISSING ON = IF COL2 NE LAST COL2 THEN COL2 ELSE MISSING;
COL3_N/A2  MISSING ON = IF COL3 NE LAST COL3 THEN COL3 ELSE MISSING;
COL4_N/A10 MISSING ON = IF COL4 NE LAST COL4 THEN COL4 ELSE MISSING;
COL5_N/A2  MISSING ON = IF COL5 NE LAST COL5 THEN COL5 ELSE MISSING;
COL6_N/I4  MISSING ON = IF COL6 NE LAST COL6 THEN COL6 ELSE MISSING;
END

SET NODATA = ' '

TABLE FILE TESTDATA
PRINT
      COL1_N
      COL2_N
      COL3_N
      COL4_N
      COL5_N
      COL6_N
BY COL1 NOPRINT
BY COL2 NOPRINT
BY COL3 NOPRINT
BY COL4 NOPRINT
BY COL5 NOPRINT
BY COL6 NOPRINT
END

That way, you get to "hide" duplicate values regardless of which column they appear in.

Regards,
- Neftali.

Well, I think Francis just beat me to the answer

KK, Use this code to simulate your data based on the CAR table. You can then use this TESTDATA with the code I had just sent you.

DEFINE FILE CAR
CTR/I2 WITH CAR = CTR + 1;
COL1/A1 WITH CAR  = DECODE CTR (1 'A' 2 'B' 3 'B' ELSE 'C');
COL2/A1 WITH CAR  = DECODE CTR (1 'T' 2 'Z' 3 'Z' ELSE 'K');
COL3/A2 WITH CAR  = DECODE CTR (1 'E1' 2 'F1' 3 'F1' ELSE 'D1');
COL4/A10 WITH CAR = DECODE CTR (1 'Paper' 2 'Metals' 3 'Metals' ELSE 'Food');
COL5/A2 WITH CAR  = DECODE CTR (1 'V1' 2 'V2' 3 'V3' ELSE 'V4');
COL6/I4 WITH CAR  = DECODE CTR (1 10 2 20 3 20 ELSE 100);
END
-*
TABLE FILE CAR
PRINT 
      COL1
      COL2
      COL3
      COL4
      COL5
      COL6
BY CTR
WHERE RECORDLIMIT EQ 4
ON TABLE HOLD AS TESTDATA
END

quote:

BY HIGHEST 10 BUSINESS_PLAN_AMOUNT NOPRINT

It might have to do with this line. Uncomment it and see what the report looks like. An option would be to 'hold' the output sorted in that order and then to a TABLEF on the hold file.

You might give this code a try.

TABLE FILE HOLDEU
 SUM 
 COMPUTE MKT_SEG_DESC/A?? = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND
                               LAST REGDESC EQ REGDESC THEN '' ELSE MARKET_SEGMENT_DESC ; AS ''
 COMPUTE BUS_PLN_AMT/F??  = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND
                               LAST REGDESC EQ REGDESC THEN '' ELSE BUSINESS_PLAN_AMOUNT ; AS ''
 COMPUTE YTD_SLS/F??      = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND
                               LAST REGDESC EQ REGDESC THEN '' ELSE YTD_SALES ; AS ''
 COMPUTE T/F8C            = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND
                               LAST REGDESC EQ REGDESC THEN '' ELSE YTD_SALES/BUSINESS_PLAN_AMOUNT * 100 ; AS ''
 COMMENTS AS ''
 BY HIGHEST 10 BUSINESS_PLAN_AMOUNT NOPRINT
 BY EUNAME
 BY SALES_REP_NAME AS ''
 BY REGDESC AS ''
 BY CUSTOMER_NAME AS ''
 ON TABLE SET NODATA ' '
 ON TABLE SET BYDISPLAY OFF
END

This message has been edited. Last edited by: Dan Satchell,

Hi All,

Thank you for your suggestions.

I have tried couple solutions, but I am getting a "Result of expression is not compatible" error on business plan amount field as it is a numeric field.

Dan, I have tried yours and I am getting this error
0 NUMBER OF RECORDS IN TABLE= 13 LINES= 13
(BEFORE TOTAL TESTS)
(FOC3259) EXL2K FORMULA: UNRECOGNIZED OPERAND OR OPERATOR
(FOC3263) EXL2K FORMULA: CANNOT CONSTRUCT EXPRESSION FOR FIELD MKT_SEG_DESC
(FOC3298) ERROR FOUND IN A COMPOUND REPORT


Am I missing something ? Here is the code

TABLE FILE HOLDEU
 SUM 
 COMPUTE MKT_SEG_DESC/A50V = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN '' ELSE NEO_MARKET_SEGMENT_DESC ; AS ''
 COMPUTE BUS_PLN_AMT/I4  = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN 0 ELSE BUSINESS_PLAN_AMOUNT ; AS ''
 COMPUTE YTD_SLS/I4      = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN 0 ELSE YTD_SALES ; AS ''
 COMPUTE T/F8C            = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND  LAST REGDESC EQ REGDESC THEN 0 ELSE YTD_SALES/BUSINESS_PLAN_AMOUNT * 100 ; AS ''
 COMMENTS AS ''
 BY HIGHEST 10 BUSINESS_PLAN_AMOUNT NOPRINT
 BY EUNAME
 BY SALES_REP_NAME AS ''
 BY REGDESC AS ''
 BY CUSTOMER_NAME AS ''


HEADING
"End User<+0>Terr/Mgr<+0>Region/District<+0>Channel<+0>Segment<+0>BP$<+0>FYTD$<+0>Tracking(%)<+0> <+0>Comments/Status/Issues"
-*"Top 10 End User Report"
 ON TABLE SET NODATA ' '
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT HTML
ON TABLE PCHOLD FORMAT EXL2K FORMULA OPEN OBREAK

Please advise.

I did forget to set the COMPUTEd fields as nullable.

COMPUTE MKT_SEG_DESC/A50V MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN '' ELSE NEO_MARKET_SEGMENT_DESC ; AS ''
COMPUTE BUS_PLN_AMT/I4    MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN '' ELSE BUSINESS_PLAN_AMOUNT ; AS ''
COMPUTE YTD_SLS/I4        MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN '' ELSE YTD_SALES ; AS ''
COMPUTE T/F8C             MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN '' ELSE YTD_SALES/BUSINESS_PLAN_AMOUNT * 100 ; AS ''

Hi Ginny,

Your solution works, but there is a small problem

Col1    Col2    Col3   Col4     Col5  Col6
A        T       E1    Paper    V1     10
B        Z       F1    Metals   V2     
		              V3    20   
C        K       D1    Food     V4    100

the col6 data is displayed on the last row (see above), is it possible to display on the first row instead :-

Col1    Col2    Col3   Col4     Col5  Col6
A        T       E1    Paper    V1     10
B        Z       F1    Metals   V2     20
		              V3     
C        K       D1    Food     V4    100

Thank you.

Sorry, I have one more correction.

COMPUTE MKT_SEG_DESC/A50V MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE NEO_MARKET_SEGMENT_DESC ; AS ''
COMPUTE BUS_PLN_AMT/I4    MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE BUSINESS_PLAN_AMOUNT ; AS ''
COMPUTE YTD_SLS/I4        MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE YTD_SALES ; AS ''
COMPUTE T/F8C             MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE YTD_SALES/BUSINESS_PLAN_AMOUNT * 100 ; AS ''

Hi Dan,

I have tried that with right syntax, but still gives me the same error message

(BEFORE TOTAL TESTS)
(FOC3259) EXL2K FORMULA: UNRECOGNIZED OPERAND OR OPERATOR
(FOC3263) EXL2K FORMULA: CANNOT CONSTRUCT EXPRESSION FOR FIELD MKT_SEG_DESC
(FOC3298) ERROR FOUND IN A COMPOUND REPORT

Thank you,
KK

quote:

ON TABLE PCHOLD FORMAT EXL2K FORMULA OPEN OBREAK

If you are using the above statement in your code, it is probably the cause of the error message. Try this:

ON TABLE PCHOLD FORMAT EXL2K FORMULA

Dan,

Its a Compound Excel Report, it will not work without OPEN NOBREAK. Please correct me if I am wrong.

Thanks.

quote:

ON TABLE PCHOLD FORMAT EXL2K FORMULA OPEN OBREAK

The statement below is fine, but the one above has an error in the syntax (OBREAK).

ON TABLE PCHOLD FORMAT EXL2K FORMULA OPEN NOBREAK

Dan,

Thank you for working on this issue, but I verified the report the OBREAK, probably that was typo when posting the message.

I moved the first sum field to see if that's causing any problem, but now the error message is on BUS_PLN_AMT line.

  
TABLE FILE HOLDEU
 SUM 

COMPUTE BUS_PLN_AMT/I11    MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE BUSINESS_PLAN_AMOUNT ; AS ''
COMPUTE YTD_SLS/I11        MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE YTD_SALES ; AS ''
COMPUTE T/F8C             MISSING ON = IF LAST EUNAME EQ EUNAME AND LAST SALES_REP_NAME EQ SALES_REP_NAME AND LAST REGDESC EQ REGDESC THEN MISSING ELSE YTD_SALES/BUSINESS_PLAN_AMOUNT * 100 ; AS ''



 COMMENTS AS ''
 BY HIGHEST 10 BUSINESS_PLAN_AMOUNT NOPRINT
 BY EUNAME
 BY SALES_REP_NAME AS ''
 BY REGDESC AS ''
 BY NEO_MARKET_SEGMENT_DESC AS ''
 BY CUSTOMER_NAME AS ''


HEADING
"End User<+0>Terr/Mgr<+0>Region/District<+0>Segment<+0>Channel<+0>BP$<+0>FYTD$<+0>Tracking(%)<+0> <+0>Comments/Status/Issues"
-*"Top 10 End User Report"
 ON TABLE SET NODATA ' '
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
-*ON TABLE PCHOLD FORMAT HTML
ON TABLE PCHOLD FORMAT EXL2K FORMULA OPEN NOBREAK

KK,

I don't see any errors with the COMPUTEs. I tested similar code using the CAR file and had no problems.

You are getting an invalide operand or operator. That means WF is not recognising something in the line. Try turning your code around so that you are comparing the current field to the last one and not the other way around and with parenthesis around the components.


COMPUTE BUS_PLN_AMT/I11 MISSING ON = IF ((EUNAME EQ LAST EUNAME) AND (SALES_REP_NAME EQ LAST SALES_REP_NAME) AND (REGDESC EQ LAST REGDESC)) THEN MISSING ELSE BUSINESS_PLAN_AMOUNT ; AS ''

Thank you all. This is resolved.

I know you said that this was resolved (although it was not marked as such in the title) But, I didn't see this result, which I think is the simpilist of all: Please check out this solution:

TABLE FILE MyFile
SUM
COMPUTE NEW_COL6/A3 = IF COL6 EQ LAST COL6 THEN ' ' ELSE COL6 ; AS 'COL6'
BY COL1 
BY COL2 
BY COL3 
BY COL4 
BY COL5 
-*BY COL6 (not needed as I superceded it with the COMPUTEd field using the LAST funtion.)
END

Results:

COL1 COL2 COL3 COL4   COL5 COL6 
A    T    E1   Paper  V1   10 
B    Z    F1   Metals V2   20 
                      V3   
C    K    D1   Food   V4   100