How to assign index of original datatable to filtered datatable

[stefi]

Hi there,

I have a range from a original datatable (dt_Orig) which I filter based on a given ID, e.g. via the variable str_ID. By filtering the filtered datatable (dt_OrigFiltered) is created.

I have to process all rows in dt_OrigFiltered whereby the original cell references in dt_Orig should be retained because I have to write back values in dt_Orig.

Example of dt_Orig with headers:

The local value of dt_Orig is in the example as follows:
1 [ID,amount,edited
2 1,0,
3 1,0
4 1,0
5 2,1
6 ]

The local value of dt_OrigFiltered based on given ID (1) is in the example as follows:
1 [ID,amount,edited
2 1,0,
3 1,0
4 1,0
5 ]

In the loop for ID = 1 the Excel file (= input for dt_Orig) should be written back as follows, here in the example in the cells C2,C3 and C4:

I already created following code:

Problem in the code:
The index for the original cell reference (int_OrigRowIndex) is for the first Row item in dt_OrigFiltered always -1 and does also not change in case of more than 1 Row items within the ForEach Loop which cause the error that the assigning in the dt_Orig cannot be processed with index -1 and only the first Row item will be written back.

Do you know what is wrong in the Assign activity for int_OrigRowIndex?
or
how I can process the dt_OrigFiltered in another way whereby the original cell references in dt_Orig are retained?

Thank you for your help!!!

[Anil_G (Anil Gorthi)]

@stefi

as two datatables are different it might not identify…Also as all the rows have same data also it is difficult with only values as well

in this case better to first add a column index and use another loop before filtering and in loop just assign index or row number to each column currentrow("Index") = currentindex.now index will have row numberof each row whcih you can use in further loop

dt.Rows(Cint(currentrow("Index")))("edited") = "ignoredrow"

cheers

[stefi]

Thank you for your answer!!!

Did I understand it correctly as follows?

1. First, the additional column „Index“ should be added to the existing datatable dt_Orig, for example in the beginning as first column via activity AddColumnToDataTable?
2. Second, loop through dt_Orig and assign the row index to column „index“ - how do you recognize the value of currentindex? Is currentindex a variable, if yes which type?
3. Once all rows in dt_Origin are assigned with row index in column „index“, the datatable can be filtered to dt_OriginFiltered.
4. Then loop through dt_OroginFiltered for processing and write the value back directly in dt_Origin (correct?) with identifier from column „index“
5. Is it necessary, to delete after looping the column „index“ as it is a help column?

Thank you for your confirmation!!

[Eric_Alvarado]

Hi @stefi
You can use dt_Orig.DefaultView.RowFilter it will create a temporal filtered table but keeping the original indexes; so you don’t need to create a 2nd dt variable

Assign ‘Filter dt with given ID’
dt_Orig.DefaultView.RowFilter "ID = " & str_ID

The following assign will convert the filtered rows in your second dt. However, this could be optional as you can directly interact with dt_Orig.DefaultView.ToTable.Rows in the foreach

Assign ‘Table keeping original indexes’
dt_OrigFiltered = dt_Orig.DefaultView.ToTable

Result:

I used for printing the table
String.Join(Environment.NewLine, dt_Orig.Rows.Cast(of DataRow).Select(Function(row) String.Join(" | ",row.itemArray)))

[Anil_G (Anil Gorthi)]

@stefi

1. Yes
2. Currentindex is a variable that is already available in for loop…if not check in for loop properties you would find index…assign a variable to it
3. Yes
4. Yes
5. You need not delete unless you are writing it back to aome excel and you dont want it to be shown

Cheers

[sudster]

Hi @stefi , if ID = 1 and Amount <> 0, what should happen?

I don’t see the benefit in creating a filtered dt. May be there are some requirements?

[stefi]

Hi sudster,
Thank you for your reply. There are many requirements which I not mentioned here as it would be too much and does not have something to do with the initial problem.

I have now a solution for it. Thank you very much anyway!

[stefi]

Hi Eric_Alvarado,

Thank you very much for your answer. This is a really charming solution due to its brevity. Unfortunately, it does not completely work in my case because always the first item was assigned in the dt_Orig and sometimes it must not be assigned the first item…

I have a solution now. Thank you very much anyway!

[stefi]

@Anil_G
I have an additional question to your solution.

Do you have an idea how I can add following logic into the solution:

If the ID-groups have more than 1 group-item (e.g. ID = 1 with in total 3 items, see screenshot on the top), how can I create an indeces list with the reference to the dt_Origin?

In some cases I need the range of the concerned ID items in order to write back the same info (that is only known after the last item has been processed) to all ID items in some excel.

I would be happy about an idea

[Anil_G (Anil Gorthi)]

@stefi

if you need all row indices of any id you can use this

listIntegervariable = dt_Origin.AsEnumerable.Where(function(x) 
x("ID").ToString.Equals("RequiredID")).Select(function(x) Cint(x("Index").ToString)).ToList

cheers