11-13-2018 8:36 AM
Hello,
I am new to ABAP and have created a program which uploads data from an excel and displays the values in ALV.
Now I want to add a new Column in my ALV output, that column should show the message "Duplicate" if there is a Duplicate row in the Excel. (The Duplicate message should be displayed in the Comment section of the Duplicate Row).
Below is my code which displays the data from excel, Selection Screen & Output Screen.
REPORT zreaddoc.
TYPE-POOLS: truxs, slis, vrm.
TYPES: BEGIN OF t_excel,
name(30) TYPE c,
surname(30) TYPE c,
role(30) TYPE c,
gender(10) TYPE c,
team(30) TYPE c,
END OF t_excel.
TYPES: BEGIN OF t_final,
name(30) TYPE c,
surname(30) TYPE c,
role(30) TYPE c,
gender(10) TYPE c,
team(30) TYPE c,
comment(30) TYPE c,
END OF t_final.
DATA: it_excel TYPE STANDARD TABLE OF t_excel,
wa_excel TYPE t_excel.
DATA: it_final TYPE STANDARD TABLE OF t_final,
wa_final TYPE t_final.
DATA: it_raw TYPE truxs_t_text_data.
DATA: it_fieldcat TYPE slis_t_fieldcat_alv,
wa_fieldcat TYPE slis_fieldcat_alv.
DATA: name TYPE vrm_id,
list TYPE vrm_values,
value LIKE LINE OF list.
CONSTANTS: c_trainee TYPE string VALUE 'Trainee',
c_se TYPE string VALUE 'Software Engineer',
c_sse TYPE string VALUE 'Sr Software Engineer',
c_tl TYPE string VALUE 'Team Lead',
c_pm TYPE string VALUE 'Project Manager'.
SELECTION-SCREEN: BEGIN OF BLOCK x WITH FRAME TITLE text-010.
PARAMETERS: p_file TYPE rlgrap-filename,
p_drpdwn(20) AS LISTBOX VISIBLE LENGTH 20.
SELECTION-SCREEN: END OF BLOCK x.
AT SELECTION-SCREEN OUTPUT.
name = 'P_DRPDWN'.
value-key = '1'.
value-text = c_trainee.
APPEND value TO list.
value-key = '2'.
value-text = c_se.
APPEND value TO list.
value-key = '3'.
value-text = c_sse.
APPEND value TO list.
value-key = '4'.
value-text = c_tl.
APPEND value TO list.
value-key = '5'.
value-text = c_pm.
APPEND value TO list.
CALL FUNCTION 'VRM_SET_VALUES'
EXPORTING
id = name
values = list.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL FUNCTION 'F4_FILENAME'
EXPORTING
field_name = 'P_FILE'
IMPORTING
file_name = p_file.
START-OF-SELECTION.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
i_line_header = 'X'
i_tab_raw_data = it_raw
i_filename = p_file
TABLES
i_tab_converted_data = it_excel[]
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
IF p_drpdwn = '1'.
LOOP AT it_excel INTO wa_excel WHERE role = c_trainee.
wa_final = wa_excel.
APPEND wa_final TO it_final.
CLEAR wa_final.
ENDLOOP.
ELSEIF p_drpdwn = '2'.
LOOP AT it_excel INTO wa_excel WHERE role = c_se.
wa_final = wa_excel.
APPEND wa_final TO it_final.
CLEAR wa_final.
ENDLOOP.
ELSEIF p_drpdwn = '3'.
LOOP AT it_excel INTO wa_excel WHERE role = c_sse.
wa_final = wa_excel.
APPEND wa_final TO it_final.
CLEAR wa_final.
ENDLOOP.
ELSEIF p_drpdwn = '4'.
LOOP AT it_excel INTO wa_excel WHERE role = c_tl.
wa_final = wa_excel.
APPEND wa_final TO it_final.
CLEAR wa_final.
ENDLOOP.
ELSEIF p_drpdwn = '5'.
LOOP AT it_excel INTO wa_excel WHERE role = c_pm.
wa_final = wa_excel.
APPEND wa_final TO it_final.
CLEAR wa_final.
ENDLOOP.
ELSE.
LOOP AT it_excel INTO wa_excel.
wa_final = wa_excel.
APPEND wa_final TO it_final.
CLEAR wa_final.
ENDLOOP.
ENDIF.
SORT it_final by name.
*Build field catalog
wa_fieldcat-fieldname = 'NAME'.
wa_fieldcat-seltext_m = 'Name'.
APPEND wa_fieldcat TO it_fieldcat.
wa_fieldcat-fieldname = 'SURNAME'.
wa_fieldcat-seltext_m = 'Surname'.
APPEND wa_fieldcat TO it_fieldcat.
wa_fieldcat-fieldname = 'ROLE'.
wa_fieldcat-seltext_m = 'Role'.
APPEND wa_fieldcat TO it_fieldcat.
wa_fieldcat-fieldname = 'GENDER'.
wa_fieldcat-seltext_m = 'Gender'.
APPEND wa_fieldcat TO it_fieldcat.
wa_fieldcat-fieldname = 'TEAM'.
wa_fieldcat-seltext_m = 'Team'.
APPEND wa_fieldcat TO it_fieldcat.
wa_fieldcat-fieldname = 'COMMENT'.
wa_fieldcat-seltext_m = 'Comment'.
APPEND wa_fieldcat TO it_fieldcat.
END-OF-SELECTION.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
it_fieldcat = it_fieldcat
TABLES
t_outtab = it_final
EXCEPTIONS
program_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
Excel Sheet:-
Used the Role Column as a Filter.
Selection Screen :-
Output Screen :-
The highlighted field is where I want to add the message "Duplicate" As that row is Duplicate.
Thanks a lot in advance for the help.
Best Regards,
PS
11-19-2018 10:08 AM
Hello ,
Used below Code before to display data .
Data : It_finaly like it_final,
wa_finaly like line of it_final,
ind type i.
sort : it_final, it_finaly.
DELETE ADJACENT DUPLICATES FROM it_final COMPARING ALL FIELDS .
loop at it_final into wa_final.
loop at it_finaly into wa_finaly where name = wa_final-name surname = wa_final-surname role = wa_final-role gender = wa_final-gender team= wa_final-team .
clear ind.
if sy-subrc = 0.
ind = sy-index .
if ind > 1 .
wa_finaly-comment = 'Duplicate'.
MODIFY it_finaly FROM wa_finaly TRANSPORTING comment .
endif.
endif.
endloop.
endloop.
DELETE it_finaly where comment ne 'Duplicate' .
loop at it_finaly into wa_finaly where comment = 'Duplicate'
loop at it_final into wa_final where name = wa_finaly-name surname = wa_finaly-surname role = wa_finaly-role
gender = wa_finaly-gender team= wa_finaly-team .
wa_final-comment = 'Duplicate'.
MODIFY it_final FROM wa_final TRANSPORTING comment .
endloop.
*** Used above code before display data ....
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
it_fieldcat = it_fieldcat
TABLES
t_outtab = it_final
EXCEPTIONS
program_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
11-19-2018 10:08 AM
Hello ,
Used below Code before to display data .
Data : It_finaly like it_final,
wa_finaly like line of it_final,
ind type i.
sort : it_final, it_finaly.
DELETE ADJACENT DUPLICATES FROM it_final COMPARING ALL FIELDS .
loop at it_final into wa_final.
loop at it_finaly into wa_finaly where name = wa_final-name surname = wa_final-surname role = wa_final-role gender = wa_final-gender team= wa_final-team .
clear ind.
if sy-subrc = 0.
ind = sy-index .
if ind > 1 .
wa_finaly-comment = 'Duplicate'.
MODIFY it_finaly FROM wa_finaly TRANSPORTING comment .
endif.
endif.
endloop.
endloop.
DELETE it_finaly where comment ne 'Duplicate' .
loop at it_finaly into wa_finaly where comment = 'Duplicate'
loop at it_final into wa_final where name = wa_finaly-name surname = wa_finaly-surname role = wa_finaly-role
gender = wa_finaly-gender team= wa_finaly-team .
wa_final-comment = 'Duplicate'.
MODIFY it_final FROM wa_final TRANSPORTING comment .
endloop.
*** Used above code before display data ....
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
it_fieldcat = it_fieldcat
TABLES
t_outtab = it_final
EXCEPTIONS
program_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
11-19-2018 1:48 PM
Hello Jafar,
Thanks a lot for the reply, your code was really helpful for me.
But I am facing a small issue. The value of sy-index is always 0. Is there any way where I can use counter ?
For eg:- Counter value for all unique rows will be 0, but the value will keep increasing for duplicate rows (0, 1, 2, ....)
Again thanks for the help.
Best Regards,
P S
11-19-2018 1:59 PM
11-20-2018 8:37 AM
Hello Jafar,
I got the solution.
The code you provided to me helped me go further.
All I had to do was use the below snippet.
SORT : it_final BY name surname.
LOOP AT it_final INTO wa_final.
AT NEW role.
CLEAR count.
ENDAT.
count = count + 1.
IF count > 1 .
wa_final-comment = 'Duplicate'.
MODIFY it_final FROM wa_final TRANSPORTING comment .
ENDIF.
ENDLOOP.
The above code triggers the counter only if there are any duplicate values (Comparing name, surname & role).
I am getting the required output now 🙂
Thanks again for the help 🙂
Best Regards,
P S
11-21-2018 7:55 AM
pratikshetty you know your code is working by pure fate, right?
You sort a table by NAME and SURNAME and you operate the break on ROLE.
If you hava a situation like
Simone Milesi Developer
Simone Milesi Software Engineer
Simone Nicoli Software Engineer
Simone Milesi will not be marked as duplicate but it will be Simone Nicoli.
11-20-2018 2:24 AM
you can try like below:
*TYPE-POOLS: truxs, slis, vrm.
TYPES: BEGIN OF t_excel,
name(30) TYPE c,
surname(30) TYPE c,
role(30) TYPE c,
gender(10) TYPE c,
team(30) TYPE c,
END OF t_excel,
BEGIN OF t_final,
name(30) TYPE c,
surname(30) TYPE c,
role(30) TYPE c,
gender(10) TYPE c,
team(30) TYPE c,
comment(30) TYPE c,
END OF t_final.
DATA: it_excel TYPE STANDARD TABLE OF t_excel,
* wa_excel TYPE t_excel,
it_final TYPE STANDARD TABLE OF t_final,
wa_final TYPE t_final,
it_raw TYPE truxs_t_text_data,
it_fieldcat TYPE slis_t_fieldcat_alv,
wa_fieldcat TYPE slis_fieldcat_alv.
DATA: name TYPE vrm_id,
list TYPE vrm_values,
value LIKE LINE OF list.
CONSTANTS: c_trainee TYPE string VALUE 'Trainee',
c_se TYPE string VALUE 'Software Engineer',
c_sse TYPE string VALUE 'Sr Software Engineer',
c_tl TYPE string VALUE 'Team Lead',
c_pm TYPE string VALUE 'Project Manager',
c_all TYPE string VALUE '*'.
SELECTION-SCREEN: BEGIN OF BLOCK x WITH FRAME TITLE text-010.
PARAMETERS: p_file TYPE rlgrap-filename,
p_drpdwn(20) AS LISTBOX VISIBLE LENGTH 20.
SELECTION-SCREEN: END OF BLOCK x.
AT SELECTION-SCREEN OUTPUT.
name = 'P_DRPDWN'.
list = VALUE #( ( key = '1' text = c_trainee )
( key = '2' text = c_se )
( key = '3' text = c_sse )
( key = '4' text = c_tl )
( key = '5' text = c_pm ) ).
CALL FUNCTION 'VRM_SET_VALUES'
EXPORTING
id = name
values = list.
AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.
CALL FUNCTION 'F4_FILENAME'
EXPORTING
field_name = 'P_FILE'
IMPORTING
file_name = p_file.
START-OF-SELECTION.
CALL FUNCTION 'TEXT_CONVERT_XLS_TO_SAP'
EXPORTING
i_line_header = 'X'
i_tab_raw_data = it_raw
i_filename = p_file
TABLES
i_tab_converted_data = it_excel[]
EXCEPTIONS
conversion_failed = 1
OTHERS = 2.
IF sy-subrc <> 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.
it_final = VALUE #( LET cond = COND #( WHEN p_drpdwn = '1' THEN c_trainee
WHEN p_drpdwn = '2' THEN c_se
WHEN p_drpdwn = '3' THEN c_sse
WHEN p_drpdwn = '4' THEN c_tl
WHEN p_drpdwn = '5' THEN c_pm
ELSE c_all ) IN
FOR wa_excel IN it_excel WHERE ( role CP cond )
( name = wa_excel-name
surname = wa_excel-surname
role = wa_excel-role
gender = wa_excel-gender
team = wa_excel-team
comment = COND #( WHEN line_exists( it_final[ name = wa_excel-name
surname = wa_excel-surname
role = wa_excel-role
gender = wa_excel-gender
team = wa_excel-team ] ) THEN 'X' ) ) ).
SORT it_final BY name.
*Build field catalog
it_fieldcat = VALUE #( ( fieldname = 'NAME' seltext_m = 'Name' )
( fieldname = 'SURNAME' seltext_m = 'Surname' )
( fieldname = 'ROLE' seltext_m = 'Role' )
( fieldname = 'GENDER' seltext_m = 'Gender' )
( fieldname = 'TEAM' seltext_m = 'Team' )
( fieldname = 'COMMENT' seltext_m = 'Comment' ) ).
END-OF-SELECTION.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
it_fieldcat = it_fieldcat
TABLES
t_outtab = it_final
EXCEPTIONS
program_error = 1
OTHERS = 2.
IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
* WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
11-20-2018 8:44 AM
Hello,
Thanks for the reply, but I got my solution.
I just had to use the below snippet before building field catalog 🙂
SORT : it_final BY name surname.
LOOP AT it_final INTO wa_final.
AT NEW role.
CLEAR count.
ENDAT.
count = count + 1.
IF count > 1 .
wa_final-comment = 'Duplicate'.
MODIFY it_final FROM wa_final TRANSPORTING comment .
ENDIF.
ENDLOOP.
Thanks & Regards,
P S
11-21-2018 12:35 AM
ofcause there are various ways to achive your requirement, but think about the effective of code you can see that after you get data from it_excel to it_final you loop into it_final and count is not best practice. code I gave you just rewrite in the new way of abap (that maybe you not familiar yet) but if you prefer it old way, i must tell this way would be better (just add few lines of code into your current loop):
LOOP AT it_excel INTO wa_excel.
MOVE-CORRESPONDING wa_excel TO wa_final.
READ TABLE it_final WITH KEY name = wa_excel-name
surname = wa_excel-name
role = wa_excel-role TRANSPORTING NO FIELDS.
IF sy-subrc = 0.
wa_final-comment = 'Duplicate'.
ENDIF.
APPEND wa_final TO it_final.
CLEAR wa_final.
ENDLOOP.
11-21-2018 3:02 AM
I like what you've done there, but CL_SALV_TABLE would be even better, don't you think?
11-21-2018 5:21 AM
11-21-2018 7:14 AM
Hello Quynh Doan Manh,
Thanks a lot for your suggestion. I am new to ABAP and still learning and you are right I am not much familiar with new ways of ABAP, but i would love to learn it. I will try your code and give you a feedback on it.
Once again Thanks a lot for your help 🙂
Best Regards,
P S
11-21-2018 7:44 AM
Hello Quynh Doan Manh,
I was trying your code but getting the below error. Can you please help me with it ?
Do I have to give any other reference in the code ?
Thanks & Regards,
P S
11-21-2018 7:52 AM
inline declaration (where you got error) is available from netweaver 7.40 so pls check your sap version first...