Linq query to bifurcate data table into multiple sets, based on set of similar values from different columns

Help Studio
1

Hello Everyone.

I have one of the requirement i need to build a linq for that , sharing below the requirement :

image
image558×547 22.9 KB

I have data table As show above, i have to bifurcate each datatable into multiple sets. Lets say for the first company and currency which is highlighted in yellow (company A and Currency INR) i have to bifurcate as one table from Row 2 to Row 5. And similarly i have to bifurcate row 6 to row 8 as another data table and so on .same company can occur with different currency also. as shown from row 15 to 17.
I have to divide this into multiple set of data tables based on each group of currency and company.

Can some one help me on how to achieve this using linq.

Appreciate the responses in advance.

Thanks,
Shiva

2

We can do it with a Group By

TableList | List(Of DataTable) =

(From d in YourDataTableVAr.AsEnumerable
Group d by k1=d("Company").toString.ToUpper.Trim,k2=d("Currency").toString.ToUpper.Trim into grp=Group
Select t = grp.CopyToDataTable).toList
3

Create a Dictionary(of string, datatable)

For Each through your Company list

  • Filter Data Table using the currentItem, into yourDict(currentItem)

Now you will have a dictionary of datatables and they dictionary keys will be the Company values.

4

Hi @ppr . Thank for quick response.!
I have done this as first approach but it is not including row Row 5 or Row 8 or row 14 etc where we have total amount for that group of company and currency. Output should come including that row as well

6

Hi @postwick , Can you please help me understand this approach Please.?
What value does the string in dictionary will store?.
Please help me to understand the approach , so that i will give a try.

Thank you!

7

we can customize. A very simple one would be to take only the first currency codes

(From d in YourDataTableVAr.AsEnumerable
Group d by k1=d("Company").toString.ToUpper.Trim,k2=d("Currency").toString.ToUpper.Trim.substring(0,3) into grp=Group
Select t = grp.CopyToDataTable).toList
8

Variables:

image
image1057×81 6.86 KB

For Each Row in Data Table
image

myDT.DefaultView.ToTable(True,“Company”) gets you all the unique Company values from myDT, to loop through them.

Filter Data Table

image
image998×219 11.4 KB

With this as test data:
image

I will now have a Dictionary(Of String, Datatable) that has three datatables in it:

  • myDict(“A”)
  • myDict(“B”)
  • myDict(“C”)

Each will have only the rows for that company.

For example, if you now wanted to loop through the rows for the datatable containing Company A, you’d use For Each Row in Datatable and give it myDict(“A”) as the datatable to loop through.

9

Okay , but will it work if the company repeats with different currency. Let’s say A with USD(as we already have A with INR). since we are pushing Company as key in dictionary.

10

Yes. The DefaultView.ToTable(True,“Company”) expression gives back a datatable containing just three rows with Company A, B, and C. So the loop executes three times and creates three datatables in the dictionary containing the rows for each company.

11

Hi @ppr , @postwick

To add on to the above requirement and to clarify more.
If you see the screenshot shared. I have first company as A and Currency INR. I wanted to split this from row 2 to Row 5 as one data table.
And again from row 6 to row 8 one data table, row 9 to row 14 one data table and row 15 to 17 one data table. Any company can occur once or more than once. When it is more than one time , then it will always be with different currency.
Hope this gives better understanding, than previous.

12

You want it to become dynamic? Meaning, in the future there will be more currency besides INR, USD, EUR and JPY? The number of Data Table varies on the different types of Currency? Is it needed to be store on a Data Table or are you going to write in on an Excel File?

13

Sorry I wasn’t able to implement the Dynamic Data Table variable.
If you find out how to do dynamic Data Table variables let me know too.

Assuming that you will write the output on excel file with different sheets.
Check this workflow:
LINQ Bifurcate.xaml (23.1 KB)
ExcelFile.xlsx (8.1 KB) - Put this Template Excel File on ‘Documents’ if you are going to run this xaml file.

This was the sample output:

image
image341×652 11.8 KB

Excel Output File:
Output_ExcelFile.xlsx (11.0 KB)

14

Hi @ton
Thanks for you quick response!. Hope this will help me

Can you please let me know what are the steps and what linq query you are using as i am not able to open the document.

thanks,
Shiva

15

Sadly, I didn’t used any LINQ, it’s just series of steps with Data Table activities.

First I did copy the template ExcelFile.xlsx for Output_Excel.xlsx (for final output)
image

Secondly, I built the input data table (dtInput) as shown on the screenshot.
image

Then I used 3 steps to capture all the possible Currency Types by using these steps:

  1. Filter Data Table
  • I did output columns to retain the ‘Currency’ column only, then I assigned it on dtCurrency
    image
    image543×570 16.4 KB
  1. Another Filter Data Table
  • Filter the dtCurrency to Remove the rows that contains ‘Total’
    image
  1. Remove Duplicate Rows
  • This is to retain one copy of each currency Type

Then, the next process is to capture the transactions by Currency Type

  • I used For Each Row in dtCurrency
    image
    image720×781 22 KB
  • Then Filter Data Table to filter out transactions by each Currency Type (dtInput filtered out to dtOutput)
    image
  • Then Duplicate Sheet activity to make a new sheet with the current Currency Type
    image
  • Write Range / Write Data Table the dtOutput on Output_ExcelFile.xlsx
    image
16

Yes, in future currencies will vary and also i am not storing in any excel file, instead i am storing each data bifurcated tables into collection : List from there i am iterating through each item in list and adding data to the queue

17

Statement given above is doing it

with the substring Addition IT IS also Handling the total inclusion into the groupings