![]() |
||||||||||||
Go ![]() | New ![]() | Search ![]() | Notify ![]() | Tools ![]() | Reply ![]() | ![]() |
Member |
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.4This message has been edited. Last edited by: juanka777, 7.6.4, Windows HTML, EXCEL | ||
|
Virtuoso |
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
| |||||||||
|
Member |
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 | |||
|
Guru |
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 | |||
|
Member |
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 | |||
|
Guru |
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 | |||
|
Expert |
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 ![]() Doug | |||
|
Virtuoso |
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 | |||
|
Virtuoso |
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 ENDThis message has been edited. Last edited by: Dan Satchell, WebFOCUS 7.7.05 | |||
|
Member |
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 | |||
|
Master |
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 | |||
|
Member |
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 | |||
|
Powered by Social Strata |
![]() | Please Wait. Your request is being processed... |
|