# 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 Output 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.

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.

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