Robotic Process Automation (RPA)

 View Only
  • 1.  How to Remove Duplicate Rows in Data Table

    Posted 21 days ago

    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:

    ID Name Age
    1 John 25
    2 Alice 30
    1 John 25
    3 Bob 28

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

    ID Name Age
    1 John 25
    2 Alice 30
    3 Bob 28

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

    Thank you for your help!



    ------------------------------
    Anas lateh
    ------------------------------


  • 2.  RE: How to Remove Duplicate Rows in Data Table

    Posted 21 days ago

    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



    ------------------------------
    NIGEL CROWTHER
    ------------------------------



  • 3.  RE: How to Remove Duplicate Rows in Data Table

    Posted 20 days ago
    Edited by Milan Babčanec 20 days ago

    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
    ------------------------------