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] Displaying one field with multiple values

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Displaying one field with multiple values
 Login/Join
 
Member
posted
Hello All,

I am new to this forum and I am hoping anyone can help me solve an issue I have with a report which gets data off SAP.

There is one field called CONTACT_PERSON that has multiple values when it comes from SAP. I am trying to display that field in one row with whatever multiples names it has in different columns. I am stuck, I am not sure how to do this.

WebFocus Server 7.6.4

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


7.6.4, Windows
HTML, EXCEL
 
Posts: 5 | Registered: September 09, 2009Report This Post
Virtuoso
posted Hide Post
First, you need to know how to seperate one name from another. This 'should' be a unique character or set of characters. Next you can create defined fields that use the GETTOK routine to collect the different names in consecutive fields. Look up it's usage notes in the manual.


GamP

- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
 
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007Report This Post
Member
posted Hide Post
Hello GamP,

Thanks for your suggestion but I don't think this will work in this situation. Basically, I have field CONTACT_NAME which has two values like the ones below.

JOHN DOE
ZUHAIR A. MOHAMMED

This is some of the code someone suggested to used in order to extract the two values.

NAME1/A80=IF NAME1 EQ '' THEN CONTACT_NAME ELSE NAME1;
NAME2/A80=IF NAME1 NE '' AND NAME1 NE CONTACT_NAME THEN CONTACT_NAME ELSE '';

The issue now is that it prints in two rows instead of one even though I am printing NAME1 and NAME2 accordingly. I can't do sum because I have way too many other fields in this report and it gives an error every time. I am not sure what else to do in order to have it print on one row. The user only wants this report to be export to excel. Any other suggestions please?


7.6.4, Windows
HTML, EXCEL
 
Posts: 5 | Registered: September 09, 2009Report This Post
Guru
posted Hide Post
Is there a limitation to the number of occurrences?

Does your MFD contain an occurs for this field?


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Member
posted Hide Post
Hello Glenda,

Right now, there is a limit to two occurrences max.

No, it doesn't contain occur.

Thanks


7.6.4, Windows
HTML, EXCEL
 
Posts: 5 | Registered: September 09, 2009Report This Post
Guru
posted Hide Post
There me be a simplier way but this is how we do it.

SET BYDISPLAY = ON
-SET &PASS    = 0 ;
-*
-DREDO
-*
-SET &PASS = &PASS + 1;
-IF  &PASS = 3 GOTO AJOINS;
-SET &SV = 'PASS'||&PASS ;
-*
DEFINE FILE CLMDER
DERCLAIM/A10 = DERCLNO||DERWKNO;
CNTR/I9 = IF DERCLAIM EQ LAST DERCLAIM THEN LAST CNTR + 1 ELSE 1;
DRMK&PASS.EVAL/A2 = DERRMKCD;
END
-*
TABLE FILE CLMDER
PRINT DERRMKCD
      DERCLNO
      DERWKNO
DRMK&PASS.EVAL
BY DERCLAIM
WHERE CNTR EQ &PASS.EVAL
ON TABLE HOLD AS &SV
END
-*
-GOTO DREDO
-*
-*
-AJOINS
-*
JOIN CLEAR *
-*
SET ALL = ON
-*
JOIN
DERCLAIM IN PASS1 TO ALL DERCLAIM IN PASS2 AS J1
END
-*
TABLE FILE PASS1
PRINT
DRMK1
DRMK2
BY PASS1.DERCLAIM
ON TABLE PCHOLD FORMAT EXL2K
END
-*
-EXIT


Glenda

In FOCUS Since 1990
Production 8.2 Windows
 
Posts: 301 | Location: Galveston, Texas | Registered: July 07, 2004Report This Post
Expert
posted Hide Post
Perhaps there is some indicator, in another column, which makes these names unique / distinguishable? And, that needs to be queried in conjunction with the name?

Just a thought (which I hope is helpful Smiler),
Doug




   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
Virtuoso
posted Hide Post
Here's an approach that might work under the right circumstances (i.e., all data in one table). I used the CAR file for this example and limited the output to 3 models per car. The LIST verb numbers each of the MODELs for easier identification in the subsequent DEFINEs. Note that if you have more than one key in your table, you must sort first by the lowest key that is appropriate for the field/column you want to target (CONTACT_PERSON in your case). This is the reason I sort by CAR first and then by COUNTRY in my first TABLE FILE, because MODEL is related to CAR, not COUNTRY. You must use the MAX. operator with the DEFINE fields in conjunction with the SUM verb in the second query, but FST. or LST. can be used for other columns needed in the query because all other values should be identical for records with the same keys. Use of the file qualifier with the LIST name (HOLD1.LIST) is required to keep the parser from getting confused by use of the keyword LIST. If you must JOIN to other tables this approach probably won't work unless you operate on the one table first before joining to the others (which won't be efficient if the other tables are in SAP - unless they are very small tables).

TABLE FILE CAR
 LIST MODEL
 BY CAR
 BY COUNTRY
 ON TABLE HOLD AS HOLD1
END
-
DEFINE FILE HOLD1
 MODEL1/A24 = IF (HOLD1.LIST EQ 1) THEN MODEL ELSE '';
 MODEL2/A24 = IF (HOLD1.LIST EQ 2) THEN MODEL ELSE '';
 MODEL3/A24 = IF (HOLD1.LIST EQ 3) THEN MODEL ELSE '';
END
-*
TABLE FILE HOLD1
 SUM
  MAX.MODEL1 AS 'MODEL #1'
  MAX.MODEL2 AS 'MODEL #2'
  MAX.MODEL3 AS 'MODEL #3'
 BY COUNTRY
 BY CAR
 WHERE HOLD1.LIST LE 3 ;
END


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Virtuoso
posted Hide Post
Here's an easier approach than the one I posted earlier. I concatenate the key fields together to make the DEFINEs much easier to code. Again, you can use the FST. or LST. operators with other columns that must also be included in the output. This is particularly true for numeric fields to avoid summing duplicate values. This approach will probably work in conjunction with JOINs to other tables.

DEFINE FILE CAR
 KEY/A26 = COUNTRY | CAR ;
 MODEL1/A24 = IF (KEY NE LAST KEY)   THEN MODEL ELSE LAST MODEL1 ;
 MODEL2/A24 = IF (KEY NE LAST KEY)   THEN '' ELSE
              IF (LAST MODEL2 EQ '') THEN MODEL ELSE LAST MODEL2 ;
 MODEL3/A24 = IF (KEY NE LAST KEY)   THEN '' ELSE
              IF (LAST MODEL2 EQ '') THEN '' ELSE
              IF (LAST MODEL3 EQ '') THEN MODEL ELSE LAST MODEL3 ;
END
-*
TABLE FILE CAR
 SUM
  LST.MODEL1
  LST.MODEL2
  LST.MODEL3
 BY COUNTRY
 BY CAR
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
Member
posted Hide Post
quote:
Originally posted by Dan Satchell:
Here's an approach that might work under the right circumstances (i.e., all data in one table). I used the CAR file for this example and limited the output to 3 models per car. The LIST verb numbers each of the MODELs for easier identification in the subsequent DEFINEs. Note that if you have more than one key in your table, you must sort first by the lowest key that is appropriate for the field/column you want to target (CONTACT_PERSON in your case). This is the reason I sort by CAR first and then by COUNTRY in my first TABLE FILE, because MODEL is related to CAR, not COUNTRY. You must use the MAX. operator with the DEFINE fields in conjunction with the SUM verb in the second query, but FST. or LST. can be used for other columns needed in the query because all other values should be identical for records with the same keys. Use of the file qualifier with the LIST name (HOLD1.LIST) is required to keep the parser from getting confused by use of the keyword LIST. If you must JOIN to other tables this approach probably won't work unless you operate on the one table first before joining to the others (which won't be efficient if the other tables are in SAP - unless they are very small tables).

TABLE FILE CAR
 LIST MODEL
 BY CAR
 BY COUNTRY
 ON TABLE HOLD AS HOLD1
END
-
DEFINE FILE HOLD1
 MODEL1/A24 = IF (HOLD1.LIST EQ 1) THEN MODEL ELSE '';
 MODEL2/A24 = IF (HOLD1.LIST EQ 2) THEN MODEL ELSE '';
 MODEL3/A24 = IF (HOLD1.LIST EQ 3) THEN MODEL ELSE '';
END
-*
TABLE FILE HOLD1
 SUM
  MAX.MODEL1 AS 'MODEL #1'
  MAX.MODEL2 AS 'MODEL #2'
  MAX.MODEL3 AS 'MODEL #3'
 BY COUNTRY
 BY CAR
 WHERE HOLD1.LIST LE 3 ;
END


This approach worked better for me, however, everytime I used SUM instead of PRINT I get the following error

(FOC1695) SAP/R3 REQUEST ERROR
: RFC_ERROR_PROGRAM
(FOC1736) SAP/R3 ERROR EXECUTING ABAP4 PROGRAM : TABLE NAME

I have tried everthing I have found on the support site but still get the error if I used SUM. If I used PRINT I get no error but then the names are displayed in different rows instead of one row. Is there any way to used PRINT and still get it to displayed in one row?


7.6.4, Windows
HTML, EXCEL
 
Posts: 5 | Registered: September 09, 2009Report This Post
Master
posted Hide Post
Another possible approach:

First of all create a new field to concatenate the two contacts with weak catenation to preserve field formatting.

NAME1/A80=IF NAME1 EQ '' THEN CONTACT_NAME ELSE NAME1;
NAME2/A80=IF NAME1 NE '' AND NAME1 NE CONTACT_NAME THEN CONTACT_NAME ELSE '';
NAME3/A160=NAME1 | NAME2;



Then create a table request and SAVE FORMAT ALPHA the resultant new field using whatever key is available for reference.

Next make a new MFD for the SAVE file with the key and two name fields, each 80 characters long.

Join the SAVE to your original data and the two names will be in separate fields for the Excel output.

As long as you use SAVE and not HOLD fot the table you can reuse the new MFD indefinitely.


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Member
posted Hide Post
quote:
Originally posted by Dan Satchell:
Here's an easier approach than the one I posted earlier. I concatenate the key fields together to make the DEFINEs much easier to code. Again, you can use the FST. or LST. operators with other columns that must also be included in the output. This is particularly true for numeric fields to avoid summing duplicate values. This approach will probably work in conjunction with JOINs to other tables.

DEFINE FILE CAR
 KEY/A26 = COUNTRY | CAR ;
 MODEL1/A24 = IF (KEY NE LAST KEY)   THEN MODEL ELSE LAST MODEL1 ;
 MODEL2/A24 = IF (KEY NE LAST KEY)   THEN '' ELSE
              IF (LAST MODEL2 EQ '') THEN MODEL ELSE LAST MODEL2 ;
 MODEL3/A24 = IF (KEY NE LAST KEY)   THEN '' ELSE
              IF (LAST MODEL2 EQ '') THEN '' ELSE
              IF (LAST MODEL3 EQ '') THEN MODEL ELSE LAST MODEL3 ;
END
-*
TABLE FILE CAR
 SUM
  LST.MODEL1
  LST.MODEL2
  LST.MODEL3
 BY COUNTRY
 BY CAR
END


I actually got it to work correctly this way, this was the easiest way to do it.

Thanks everyone for your help and your replies.


7.6.4, Windows
HTML, EXCEL
 
Posts: 5 | Registered: September 09, 2009Report 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] Displaying one field with multiple values

Copyright © 1996-2020 Information Builders