Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Duplicate Rows

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Duplicate Rows
 Login/Join
 
Gold member
posted
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,


Test - Webfocus 7.6.7
Prod - Webfocus 7.6.7
Win2003
Sql Server 2000 and 2008
 
Posts: 59 | Registered: May 01, 2007Report This Post
Expert
posted Hide Post
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.


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, 2006Report This Post
Expert
posted Hide 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


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Gold member
posted Hide Post
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


Test - Webfocus 7.6.7
Prod - Webfocus 7.6.7
Win2003
Sql Server 2000 and 2008
 
Posts: 59 | Registered: May 01, 2007Report This Post
Virtuoso
posted Hide Post
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.



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Virtuoso
posted Hide Post
Well, I think Francis just beat me to the answer Wink

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



Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
Expert
posted Hide Post
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.


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, 2006Report This Post
Virtuoso
posted Hide Post
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,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
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.


Test - Webfocus 7.6.7
Prod - Webfocus 7.6.7
Win2003
Sql Server 2000 and 2008
 
Posts: 59 | Registered: May 01, 2007Report This Post
Virtuoso
posted Hide Post
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 ''


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
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.


Test - Webfocus 7.6.7
Prod - Webfocus 7.6.7
Win2003
Sql Server 2000 and 2008
 
Posts: 59 | Registered: May 01, 2007Report This Post
Virtuoso
posted Hide Post
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 ''


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
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


Test - Webfocus 7.6.7
Prod - Webfocus 7.6.7
Win2003
Sql Server 2000 and 2008
 
Posts: 59 | Registered: May 01, 2007Report This Post
Virtuoso
posted Hide Post
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


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
Dan,

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

Thanks.


Test - Webfocus 7.6.7
Prod - Webfocus 7.6.7
Win2003
Sql Server 2000 and 2008
 
Posts: 59 | Registered: May 01, 2007Report This Post
Virtuoso
posted Hide Post
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


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Gold member
posted Hide Post
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



Test - Webfocus 7.6.7
Prod - Webfocus 7.6.7
Win2003
Sql Server 2000 and 2008
 
Posts: 59 | Registered: May 01, 2007Report This Post
Virtuoso
posted Hide Post
KK,

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


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Master
posted Hide Post
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 ''


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Gold member
posted Hide Post
Thank you all. This is resolved.


Test - Webfocus 7.6.7
Prod - Webfocus 7.6.7
Win2003
Sql Server 2000 and 2008
 
Posts: 59 | Registered: May 01, 2007Report This Post
Expert
posted Hide Post
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




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Duplicate Rows

Copyright © 1996-2020 Information Builders