To fetch the unique vales with a common column name

Help Studio
, , ,
1

Fetching the Unique Column values separated with a ", " with a Common Column
querytest 1.xaml (25.9 KB)

2

@Nissy_Ruth_Prabhu

Please share the input sample and expected Output

3

Hi @Nissy_Ruth_Prabhu

You can use the below LINQ Expression,

Check the below steps,
→ Use the Read Range workbook activity to store the excel data in a datatable called Input_dt.
→ Then use the assign activity to store all the rows data with seperating comma in a Output Variable,

- Assign -> Output = String.Join(",", Input_dt.AsEnumerable.Select(Function(X) X("Column name").ToString).ToList())

The output data will store in a Output variable. Change the column name in the LINQ Expression.

Or if you want to remove the duplicate data from the rows and append the data with comma then you can use the below expression,

- Assign -> Output = String.Join(",", Input_dt.AsEnumerable.Select(Function(X) X("Column name").ToString).Distinct.ToList())

Hope it helps!!

4

Hi @Nissy_Ruth_Prabhu

resultDataTable = inputDataTable.AsEnumerable()
                    .GroupBy(Function(row) row("CommonColumn").ToString())
                    .Select(Function(group) resultDataTable.Rows.Add(
                        group.Key,
                        String.Join(", ", group.Select(Function(r) r("UniqueColumn").ToString()).Distinct())
                    )).CopyToDataTable()

Regards,

5

@Nissy_Ruth_Prabhu,

Follow this approach.

  1. Get unique values from datatable
dtOutput = dtInput.DefaultView.ToTable(True,"Column Name")
  1. Convert to comma separated string
strOutput=String.Join(",",(From row in dtOutput .AsEnumerable select cstr(row("Column Name"))).ToArray())

Thanks,
Ashok :slight_smile: