please share the complete requirements always on begin of request with us
give a try on following doing it all:
dtResult is empty has the 4 cols: ColName, FilledRows, FilledPercentage, DistinctCount
(
(From c In varInputSapDataTable.Columns.Cast(Of DataColumn)
Let cn = c.ColumnName
Let fr = varInputSapDataTable.AsEnumerable.Where(Function (x) Not (isNothing(x(cn)) OrElse String.IsNullorEmpty(x(cn).toString.Trim))).Count
Let p = (fr/varInputSapDataTable.Rows.Count*100).toString(“F2”)
Let cnt= varInputSapDataTable.AsEnumerable.Select(Function (r) r(cn).toString.Trim).Distinct().Count
Order By CDbl(p) Descending
Let ra = New Object(){cn, fr,p, cnt}
Select row=dtResult.Rows.Add(ra)).CopyToDataTable
for an updating the Unique Count col afterwards we can think about:
create a dictionary with key: ColName and value distinct count
loop over the result table and fill the Unique Count col with the help of the dictionary using the col named from looped row
Read the input excel using read range activity and store the value in the variable:
varInputSapDataTable
get the headers of the table and calculate the filled %
used a query
assign activity
LHS: in_varDataTableForColumnHeadersAndFilledRate
RHS: (From c In varInputSapDataTable.Columns.Cast(Of DataColumn)
Let fr = varInputSapDataTable.AsEnumerable.Where(Function (x) Not (isNothing(x(c)) OrElse String.IsNullorEmpty(x(c).toString.Trim))).Count
Let p = (fr/varInputSapDataTable.Rows.Count*100).toString(“F2”)
Let ra = New Object(){c.ColumnName, fr,p}
Select r=in_varDataTableForColumnHeadersAndFilledRate.Rows.Add(ra)).CopyToDataTable
sort the data table so obtained in descending order based on filled %
used a query
assign activity
LHS: in_varDataTableForColumnHeadersAndFilledRate
RHS: in_varDataTableForColumnHeadersAndFilledRate.AsEnumerable.OrderByDescending(function(r) Double.Parse(r(“Filled %”).ToString)).CopyToDataTable()
(From c In varInputSapDataTable.Columns.Cast(Of DataColumn)
Let cn = c.ColumnName
Let drFilled = varInputSapDataTable.AsEnumerable.Where(Function (x) Not (isNothing(x(cn)) OrElse String.IsNullorEmpty(x(cn).toString.Trim)))
Let cntFilled = drFilled.Count
Let p = (cntFilled/varInputSapDataTable.Rows.Count*100).toString(“F2”)
Let cntDistinct= drFilled.Select(Function (r) r(cn).toString.Trim).Distinct().Count
Order By CDbl(p) Descending
Let ra = New Object(){cn, cntFilled, p, cntDistinct}
Select row=dtResult.Rows.Add(ra)).CopyToDataTable
can we write a linq
where we have a temp data table
and then we remove duplicates for a particular column and then count the remaining rows and that would be the unique values
As you reported to different approaches that it is not giving an expected output we would suggest to analyze it within an isolated / standalone RnD xaml.
readin excel
explore different approaches on this for getting the count you are looking for.
in case of you cannot ensure the validity of the given counts, then you can also check your implementation approaches by runnong testcases with a samller / dedicated test set.
In case of you can share a reduced test set with us we can also help you on the rnd/prototypes