I want to update rows value according to Id and CustomerID only in duplicate set

Help Activities
, ,
1

Conditions:

  1. Only update value Duplicate ID & customer id
  2. Fetch all value which has CustomerId character contains in TICP column (EMP contains EMP TT)
  3. Update rows data which is blank & which is incorrect row data (e.g 6,12 row TCIP value doesn’t match to customerID)
  4. If already their value then don’t update, only update blank rows and incorrect rows data
  5. Incorrect rows data (6 and 12 row TIA KK doesn’t match EMP)
    Input:
    image
    Expected Output
    image
    @Anil_G @Yoichi
2

We would suggest to handle it has a grouped member processing

  • Group the data on CustomerID
  • process the groups
  • for the filling up the row position / a helper column maybe can help
3

Hi @suyog79 ,
my input
image

my output
image

I want to know why after MMTA is GGTA ?

4

We assume that
grafik
is a typo and TCIP Code is PA KK following the other patterns

Input:
grafik

Output:
grafik

Done with this implementation:

grafik
grafik561×910 52.7 KB

When then count of used activities is ok for you we can introduce more into our solution

5

@ppr Yeah its my mistake there is PA KK not PAKK,
Could you send me xml file

6

Lets get introduced within the solution approach

For preparation and selecting differen solution building block options we applied the technique of brain debugging. We just analysed on how we would do it by our own and map the parts to code

  • looping over rows
  • checking TICP starts with CustomerID
    • Yes, do nothing,
      memorize the Col1-3 for the TICP,
      memorize the last Specific TICP for the CustomerID
    • No, remember the last specific TICP code for CustomerID and update the fields with remembering the corresponding Col1-3

Mapping to code:

  • memorizing TCIP-Col1,2,3 Sets: → Nested Dicitionaries:
    Dictionary(Of String, Dictionary (of String, Object))
{ 
{ "MM PA", { { "Col1", "134" }, { "Col2", "41" }, { "Col3", "41" } } }, 
{ "MM CA", { { "Col1", "1" }, { "Col2", "11" }, { "Col3", "12.31" } } }, 
{ "EMP TT",{ { "Col1", "32" }, { "Col2", "13" }, { "Col3", "0.422" } } }, 
{ "TIA KK", { { "Col1", "7" }, { "Col2", "3" }, { "Col3", "44.55" } } },
{ "PA KK", { { "Col1", "4444" }, { "Col2", "5555" }, { "Col3", "5555" } } }, 
{ "GG TA", { { "Col1", "42" }, { "Col2", "52" }, { "Col3", "62" } } }, 
{ "MM TA", { { "Col1", "51" }, { "Col2", "2252" }, { "Col3", "6622" } } } 
}
  • memorizing Last Seen TCIP for CustomerID → Dictionary (of String, String)
  • Flexibility: Col1,Col2,Col3 set extensible and not mandatory subsequent

Variables:

grafik
grafik889×143 37.1 KB

Flow / Implementation - Prep:
grafik

dictLKTicp =

(From d In dtData.AsEnumerable()
Where Not (isNothing(d("TICP")) OrElse String.IsNullOrEmpty(d("TICP").toString.Trim))
Group d By k=d("TICP").ToString.Trim() Into grp = Group
Let gsf = grp.OrderByDescending(Function (g) arrColSet.Sum(Function (c) Convert.ToInt32(Not String.IsNullOrEmpty(g(c).ToString.Trim)))).First()
Let dvl = arrColSet.ToDictionary(Function (lk) lk, Function (e) gsf(e))
Select t = Tuple.Create(k,dvl)).ToDictionary(Function (t) t.Item1,Function (t) t.Item2)
  • for flexibility reasons we do rate a Col1,2,3 Set on its fillings and prefer the most complete filled one

dictCurrentTicp =

dtData.asEnumerable().Select(Function (x) x("CustomerID").toString.Trim).Distinct().ToDictionary(Function (k) k, Function (v) "")
  • Init a Dict with all distinct CustomerIDs and empty string

Processing:

grafik
grafik595×866 50 KB

Calculate CurrentTICPKey
Later we want to memize the current / last seen TICP and have to find the corresping key
e.g. for MM PA we need to calculate MM

Code

CurrentTicpKey =

dictCurrentTicp.Keys.Where(Function (x) row("TICP").toString.Trim.StartsWith(x)).DefaultIfEmpty(row("CustomerID").toString.Trim).First()
  • we are looking for all Keys (ProductIDs) for this one, where TCIP is starting with, or If empty we use the CustomerID value from row
  • We did not use a substring method to get flexible driven by the CustomerIDs

Set the last seen TICP for the corresponding Ticp Key / CustomerID
Eg set MM PA for MM, Set TIA KK for TIA

Code

dictCurrentTicp =

If(String.IsNullOrEmpty(row("TICP").toString.Trim), dictCurrentTicp(CurrentTicpKey), row("TICP").toString.Trim)
  • Use TICP Code or last seen TICP for the CurrentTCIPKey

IF - When TCIP starts not with the same row CustomerID
e.g. “” and TIA, MM TA and GG

Set TCIP row value
row(“TICP”) =
dictCurrentTicp(row(“CustomerID”).toString.Trim)

Loop
Retrieve the memorized Col1,2,3 Value Set form the dictLKTicp and set the values to the row

Code

kvp in dictLKTicp(row(“TICP”).ToString.Trim)

Set the col value using the dictionary key as column name and the dictionary value as column value

For more training / starter help also have a look here:
[HowTo] LINQ (VB.Net) Learning Catalogue - Help / Something Else - UiPath Community Forum

Understanding the 6 Debugging Panels of UiPath in the easiest way possible! - News / Tutorials - UiPath Community Forum

Kindly note: we can also decompose every LINQ into a set of essential activites. But it will need that our implementation will grow within the number of invested activities. So feel free to balance between both approaches.