Hello everyone,

I am working with a Data Table in IBM RPA and I need to remove duplicate rows. I would like to know the best way to achieve this. Should I use a built-in command, script, or is there a recommended approach?

For example, I have this data:

And I want to remove the duplicate row so that my final output is:

What is the best way to achieve this in IBM RPA?

Thank you for your help!

Hi Anas,

You could use the strDictAdd function to remove duplicates.  See line 76 of the example bot here: https://community.ibm.com/community/user/automation/viewdocument/datatransformationbot?CommunityKey=8e1faacf-120e-4d65-b013-c14cc0e27c04&tab=librarydocuments

Hello, you can also create a SQLite DB, instert the data into it and run Select distinct SQL query. Here's a simple example (not perfect, but you get the idea).

getSpecialFolder --folder "LocalApplicationData" User_Profile=value
concatTexts --text "${User_Profile}" --value "\\Temp\\RPA002.db" Path_to_DB=value
getColumns --dataTable ${Mood_Table} Columns_List=names Columns_Schema=schema
setVar --name "${Create_Table_Command}" --value "CREATE TABLE ${Temp_Table_Moods} ( "
foreach --collection "${Columns_List}" --variable "${Columns_Text}"
concatTexts --text "${Create_Table_Command}" --value "`${Columns_Text}` TEXT," Create_Table_Command=value
endFor
getStringLength --text "${Create_Table_Command}" Create_Table_Length=value
evaluate --expression "${Create_Table_Length}-1" Create_Table_Length=value
getSubString --text "${Create_Table_Command}" --start 1 --length ${Create_Table_Length} Create_Table_Command=value
concatTexts --text "${Create_Table_Command}" --value " );" Create_Table_Command=value
sqliteConnect --createNew  --sql "${Create_Table_Command}" --path "${Path_to_DB}" SQLite_PathToDB=path SQLite_Connection_String=connectionString SQLite_Connection=connection SQLite_Success=success
sqlBulkInsert --dataTable ${Mood_Table} --connection ${SQLite_Connection} --tablename "${Temp_Table_Moods}" --mappings "number==" --timeout "00:20:00" SQL_Insert_Success=value
if --left "${SQL_Insert_Success}" --operator "Is_True" --negate 
failTest --message "Cannot insert lines to table ${Temp_Table_Moods}."
endIf
replaceText --texttoparse "${SQL_SQLite}" --textpattern "&CQ_YEAR" --replacement "${CQ_Year}" SQL_SQLite=value
replaceText --texttoparse "${SQL_SQLite}" --textpattern "&PQ_YEAR" --replacement "${PQ_Year}" SQL_SQLite=value
replaceText --texttoparse "${SQL_SQLite}" --textpattern "&CQ_MONTHS" --replacement "${CQ_Months}" SQL_SQLite=value
replaceText --texttoparse "${SQL_SQLite}" --textpattern "&PQ_MONTHS" --replacement "${PQ_Months}" SQL_SQLite=value
sqlExecuteReader --connection ${SQLite_Connection} --statement "${SQL_SQLite}" --timeout "00:05:00" SQL_Output_Table=value SQL_Number_Rows=rows SQL_Number_Columns=columns
sqlDisconnect --connection ${SQLite_Connection}

------------------------------
Milan Babčanec
------------------------------