prepare with build datatable the report datastructure (Columns: tableName, uniqueCount) - dtReport
Assign acitvity:
Left: dt Report
Right
(From x in dtData.Columns.Cast(Of DataColumn).Select(Function (c) c.ColumnName)
Let cnt = dtData.AsEnumerable.Select(Function (r) r(x).toString.Trim).Distinct().Count
Let ra = new Object(){x, cnt}
Select r=dtReport.Rows.Add(ra)).CopyToDataTable
Use Read Range activity to read the input data from excel file and it will give output as DataTable. Let’s say ‘InputDT’.
And then use Build DataTable activity and create two columns - table name and unique count. Let’s say ‘OutputDT’
And then use For Each loop activity and pass ‘InputDT’ to it.
For Each item in InputDT.Columns
Use Add DataRow activity and pass DataTable as OutputDT and ArrayRow value as below.
{item, InputDT.DefaultView.ToTable(True,item).Rows.Count}
Finally use Write Range activity and pass OutputDT to write into Excel file.
i need to add the unique value count in this already existing table
IFLOTX
Filled Rows
Filled %
Unique Count
TPLNR
198303
100.00
SPRAS
198303
100.00
PLTXT
197876
99.78
this table is generated using 2 queries
(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
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