I have csv data i want to update value with start with A- and B- only duplicate State1,State2,ID&PID

Help Activities
, ,
1

Input file:
Inputfile.xlsnt (11.9 KB)
Inputfile.xlsnt](upload://1STnLGa2wteNiXkPoIQ3P70f7qn.xlsx) (11.9 KB)

2

Hello @suyog79

Read Excel Data (Inputfile.xlsx) → Iterate Through Rows:
Check if ID, PID, State1, and State2 match previous row:
If matching:
Extract first value from column A and last value from column B
Update A-Price1, A-Price2, B-Qnt1, B-Qnt2 in the same row
Else (not matching):
Continue to the next set of data
Filter DataTable to Keep Only Last Occurrence of Each Combination
Write Output to Excel (Expected Output)

Thanks & Cheers!!!

3

Based on my understanding of the logic

You want to group record which have same pid and I’d and then you want to check each record having same state related with either A-price 1 or A price 2 like CI in 2nd row is equal to CI with A price 2 in 3rd row.

So here we have to collect those record within the group which have either same 1)state 1 and state 2 or 2)state 1 and state 1 or 3)state 2 and state 2 . I have a question what if both condition 2 and 3 are true at the same time? Because I see that A price 1 value is updated in A price 2 sometimes and sometimes it’s in 1, so there will be conflict if A price 1 and A price 2 of first record is different

Similar for B qnt

And once we group them if there are multiple same records ,do we update A-price 1 or A price 2 for all the remaining records same states?

Please correct me if i am wrong.

Based on that I may be able to help you

4

Hi @Rajkumar1 your understanding is right
for condition 2 & 3 that scenario may not be occour i want update only last row which has value

5

Hi @suyog79 ,

Is it possible to provide some more example data ?

From the Provided data, we are able to see the patterns and relations between the rows having ID & PID as 123 - MM and 555 - AA but the rows having 555 - TT is not clear.

Why was the A-Price2 Value (735) moved to A-Price1 ? We do not see this being done for the other grouped rows.

6

Hi @supermanPunch Please see the 3rd screenshot i highlighted the duplicate rows based on ID and PID.
and other thing is for column start with “A-” i want to fetch first value it would be any where ethier A-Price1 or A-Price2(only yellow highlited mark values) and same thing for “B-” also but only want to fetch last value which is green coloured highlighted

7

Hi @Anil_G Could u help me that question

8

@suyog79

Few things here

  1. If we are trying to find similar columns using combination of 4 columns then nothing is similar
  2. First value of A- to be updated where? As per output ipdated in next row of A-2
  3. Similarly last values also went into differnet rows

Need a little proper explanation

Cheers

9

Hi @Anil_G

  1. I want to check Same state value in state1 column or state2 column

  2. If duplicate value found in State column then fetch top most value of start with “A-” column and update all places of column start with “A-” only for same state
    (for ex in screenshot
    ID=123&PID=MM
    1.for state CI fetch top most value which is 413
    2.for state NY fetch top most value which is 994
    ID=555& PID=TT
    1.for state NY fetch top most value which is 735
    ID=555 & PID=AA
    1.for state PA fetch top most value which is 5413
    2.for state NJ fetch top most value which is 63

  3. After that remove all above duplicate state column value of all 6 column only i want to take for 1 id equal to 1 state

  4. At the end i want first value of column start with “A-”(A-Price1 or A-Price2) and last value of column start with “B-”(B-Qnt1 or B-Qnt2)

  5. final output look like a expected screenshot

Thank you:)

10

Hi @Anil_G @supermanPunch Did you get any solution?

11

Main.xaml (10.9 KB)

Dummy.xlsx (11.4 KB)

So I was not able to do it using standard UiPath acitvities, had to resort to invoke code.

I suggest you look into LINQ, thats what i have heavily used. Others are just standard vb.net operations. Also you should look into IEnumerable.

So the logic i have used is essentially creating a separate table consisting of a No State and Price and Actual State, Actual state is a column i added on my own which basically records what state that record is . So in this format I separate out a record’s state1 and state2 into two records. I did this for all the records and then simply merge them, then I grouped them by their state values. Then Ordered them by No and then simply updated the last object price value in its corresponding position in data table with the first object’s price value in that group .

For deletion , I simply removed that state whose value we used for deletion, Now here is an important thing. I have not deleted the entire row because there is some contradictions in the output of your data, that the 2nd row is completely deleted while 10th and 11th row are not. So for simplicity i have decided to delete only that corresponding state

one more contradiction is in row 2 ,which state’s value to use to update state in row 3? You erase 2nd state otherwise the code might behave unpredictably

12

@suyog79

Please check this…I hope this is what you need

FinalOutput
image

Intemittent table
image

BlankProcess - Copy (12).zip (17.9 KB)

Orddervalues.Xaml is the xaml you need to look into…

I did not write the data back…you can write it to excel and check…dt is the datatable which you need to write…Newdt is an intermittent table used to fetch the data as needed

to summarize…Two major loop

  1. First I am getting all the unique first and last values and the index for the rows and index of the state columns into a separate datatable
  2. Then second loop using the above constructed dt to empty the values and populate the values as needed

Both loops look alike one for getting data and other for setting data

Hope this helps

cheers

1 Like
13

Thank you so much
@Anil_G

1 Like
14

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.