Index of a value in Datatable

Help Studio
, , , ,
1

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

2

Please assist me with this, thank you

3

Hi @Matthew2
Please refer to the xaml below.

testSequence.xaml (8.7 KB)

Thanks

1 Like
4

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

5

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
6

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
7

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:

grafik
grafik630×597 16.4 KB

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

Also have a look here:

1 Like
8

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