Group by a column and assign Remark value based on conditions

Hi

I am having a situation as described below Group by and assign.xlsx (10.7 KB)

Input image

Output image

The conditions are:

  1. Group by column “RPO”
    In that group:
    a. If Any one of the Status = “To” or “Dey” then Assign Remark = “Not Avl”
    b. If not Any one of Status = “To” or “Dey” then Assign Remark = “Avl”
    c. If All of the Status = “Pack” then Assign Remark = “Avl”
    d. If any one or more and not all Status = “Pack” then assign Remark = “To check”

I tried it using if else in LINQ but it is not satisfying all conditions simultaneously.

Please help.

Hi,

You need to do it in three steps:

  1. Select unique PRO values
  2. FOR each unique value - select all records
  3. a. From the selected records select to see if any has Dey or To as Status - copy all the records in a new table with the Remark Not Avl - STOP
    b. From the selected records select to see if all has Pack as Status - copy all the records in a new table with the Remark AVI - STOP
    c. From the selected records select to see if any has Pack as Status - copy all the records in a new table with the Remark To Check - STOP
    d. Copy all the records in a new table with the Remark Avl

Hope it helps.
Regards.

@CristianZachiteanu Thanks for the reply

But can you share a linq for the same, if possible

@Swara_Soni
Lets assume following

check1 = If All of the Status = “Pack” then Assign Remark = “Avl”
check2 = If any one or more and not all Status = “Pack” then assign Remark = “To check”
check3 = If Any one of the Status = “To” or “Dey” then Assign Remark = “Not Avl”

b. If not Any one of Status = “To” or “Dey” then Assign Remark = “Avl”
we interpretate this as check1=false, check2=true, check3=false

with the expression true=1, false = 0 we can define a checkmask and map the status
grafik

so we can use it on following LINQ

(From d In dtData.AsEnumerable
Group d By k=d("RPO").toString.Trim Into grp=Group
Let chk1 = grp.All(Function (x) x("Status").toString.Trim.Equals("Pack"))
Let chk2 = grp.Any(Function (x) x("Status").toString.Trim.Equals("Pack"))
Let chk3 = grp.Any(Function (x) {"To","Dey"}.Contains(x("Status").toString.Trim))
Let msk = String.Join("",{chk1,chk2,chk3}.Select(Function (x) Convert.ToInt32(x).toString))
Let status = If(dictStatus.ContainsKey(msk),dictStatus(msk),"UNDEFINED")
From g In grp
Let ra = g.ItemArray.Append(status).ToArray
Select r = dtResult.Rows.Add(ra)).CopyToDataTable

using following flow, removing remark col from data as we can easier handle it within the LINQ
grafik

find starter help here:
GroupBy_1Col_UsingCheckMask.xaml (8.8 KB)

2 Likes

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