Index of a value in Datatable

Hi,

I want to find the index of no of occurance of a each value (posting key) in the datatable. For example: for the below scenario

image

Index Posting Key
1 - 40
1 - 19
2 - 40
2 - 19

There are 4 known posting keys, so for each posting key I have to find the index from the datatable.
Please let me know how to achieve this. Thank you
Also, the current method I implemented:

I assigned a variable “index” to “datatable.rows.indexof(row)”

Another variable “posting_key” to "datatable.rows(index)(“Posting Key”).tostring

This gives me

Index Posting Key
1 - 40
2 -19
3-40
4-19

Can I put a where condition in the above formula itself to get index for individual posting keys like below?

Index Posting Key
1 - 40
1 - 19
2 - 40
2 - 19

Please assist me with this, thank you

Hi @Matthew2
Please refer to the xaml below.

testSequence.xaml (8.7 KB)

Thanks

1 Like

Thanks for your file, I suppose you have hardcoded the index based on the row, but my requirement is based on the occurrence of the posting key. If there are 6 rows of data out of which the posting keys are like below:

Posting Key
19
40
19
50
19
50

I want to consider each posting key and give their indexes without hardcoding because it isnt in the same order everytime. Im trying to get an output based on the occurrence of posting key like below:

Index Posting Key
1 - 19
1 - 40
2 - 19
1 - 50
3 - 19
2 - 50
Please let me know of your thoughts, thank you

Can you sort your datatable on posting key and loop?

for each row:
if current value == prev value, increment index, assign index
else index=1, assign index

1 Like

You may try the following approach:

'1/ group DT by posting key
'2/ for each group reset IDX to 1
'3/ for each row in the group assign IDX and increment
dt.AsEnumerable().GroupBy(Function(x) x.Item("PK")).ToList().ForEach(
Sub(g) 
	Dim idx As Int32 =1
	g.AsEnumerable().ToList().ForEach(
	Sub(r) 
		r.SetField("IDX",idx)
		idx+=1
	End Sub)
End Sub)

IndexRowsOfGroup.zip (8.6 KB)

Cheers

1 Like

We can do with LINQ:

(From d In dtData.AsEnumerable
Group d By k= d("Posting Key").toString.Trim Into grp=Group
From t In grp.Select(Function (x,i) Tuple.Create(i,x))
Let ra = New Object(){t.Item1 + 1, t.Item2("Posting Key")}
Order By dtData.Rows.IndexOf(t.Item2)
Select dtReport.Rows.Add(ra)).CopyToDataTable

or on a hybrid approach:

find starter help here:
GroupBy_1Col_MemberCount_OrderByOrigPos.xaml (14.4 KB)

Also have a look here:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.