How to replace(row value) / remove(rows) in DataTable

[Guest]

Hi all,

I have a dataTable that contains nearly 38400 rows.
In the dataTable consist of 3 column.
column 1 Name: MUHNO
column 2 Name: HESNO
Column 3 Name: BALANCE

Let me give you some example first:

++++++++++++++++++++++++++++++++++++++++
MUHNO HESNO BALANCE
----------------------------------------------------------------
0111621000 7371626 0
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0
++++++++++++++++++++++++++++++++++++++++

Now in my DataTable I have 16000 distinct HESNO
and total rows is 38400.

What I want is I have to use the HESNO to get all
the rows as in shown above and count the balance
that has MUHNO starting 09??????? and add into the row
that has 0111621000 and delete the rest of the
3 rows that their HESNO is same but their MUHNO
that stsrts with 09????????.

So I have to count for below 3 rows balance:

MUHNO HESNO BALANCE
----------------------------------------------
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0

which is -275 and in to the row balance column:
I guess I have to replace the value of the
BALANCE column for the row:

MUHNO HESNO BALANCE
---------------------------------------------
0111621000 7371626 0


What I did is that I run 38400 rows and I put all the HESNO
into array. Then I remove duplicated value in the array
and I sort the array.

Now I have to create a for loop for array
to check in dataTable and use replace/remove operation
that I decsribe above. But Unfortunatly I am stuck in
here.

Simply the operation have to be like in below:

Before
MUHNO HESNO BALANCE
--------------------------------------------
0111621000 7371626 0


After
MUHNO HESNO BALANCE
---------------------------------------
0111621000 7371626 -275


and delete the rows:
MUHNO HESNO BALANCE
---------------------------------------
0911621000 7371626 -250
0911621100 7371626 -25
0914021000 7371626 0.

Can anyone out there to tell me easy way to achive this
in fatsest way?

I thank you all for your kind understanding to reading my post.

Rgds,
Niyazi

 

[Cor Ligthert [MVP]]

Niyazi,

This is the oldest use of dataprocessing.

Your datatable is an collection of datarows so you don't need an array.
However you can use a new datatable that you made distinct.

You can with that therefore clone that one and than it is something as here
in a kind of pseudo code.

myDistinctTable = MyOldTable.clone

create a field myPreviousKey value Is Nothing/Null
create one or more counterfields
Than you start going through your
For each row in myOldTable
if the previousKey not = CurrentKey
If not previousKey is Nothing/null
Add the new MyDistinctTableRow to that table
end if
Create new datarow
Set the key
end if
count into the new datarow
End For

 

[Guest]

Hi Cor,
Thank you for your input. My problem is that in the MUHNO table the strings
that contains 09??????? have to be remove but before remove it I have to
calculate allthe balances if their HESNO is same. Than I have to move that
balance in the MUHNO that contains the string which is doesn't start with
09?????? but it HESNO is same.

I do understand the concept that you wrote but I never done it before. So I
have bif confuse how to achive the problem I am facing, with the way I want
and combine your concept.

Here is what I did at the moment:

I create a dataRow array

tmpResultROWS = Nothing
tmpResultROWS = tmpTABLE.Select(mEXPR1, mSORT1)

then wakl throu all 38400 rows and get their HESNO into array. Then I use:

'REMOVE DUBLICATED VALUE FROM ARRAY +++++++++++++++++
Dim col As New Scripting.Dictionary
Dim ii As Integer = 0
For ii = 0 To DTHESNO_ARRAY.Length - 2
If Not col.Exists(CStr(DTHESNO_ARRAY(ii))) Then
col.Add(CStr(DTHESNO_ARRAY(ii)), ii)
End If
Next

ReDim _DTHESNOKR102A(col.Count - 1)

Dim iii As Integer = 0
For iii = 0 To col.Count - 1
_DTHESNOKR102A(iii) = col.Keys(iii)
Next

'NOW SORT THE STRING ARRAY
Array.Sort(_DTHESNOKR102A)

col = Nothing

to remove duplicated array and I sorted (But seems this section is very slow)

Then I get nearly 16500 distinct HESNO in my array.

Then I walk throu nearly 16500 HESNO as:
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
'THE UNIQUE _DTHESNOKR102A (DTHESNO ARRAY)
'tmpTABLE that contains 38399 rows

Dim i As Integer = 0
For i = 0 To _DTHESNOKR102A.Length - 1
Dim mxDTHESNO As String = ""
mxDTHESNO = _DTHESNOKR102A(i)

Dim xcEXPR1 As String = "WWWW = " & mxDTHESNO
Dim xcSORT1 As String = "WWWW ASC"
Dim resROWS As DataRow()
resROWS = Nothing
resROWS = tmpTABLE.Select(xcEXPR1, xcSORT1)

Dim myBALANCE As Decimal = 0.0
If resROWS.Length > 1 Then
Dim mLen As Integer = resROWS.Length - 1
Dim yumax As Integer = 0
For yumax = 0 To mLen
myBALANCE = myBALANCE + CDec(resROWS(yumax).ItemArray(16))
Next

Dim trimax As Integer = 0
For trimax = 0 To mLen
Dim tmpDTMHNO As String = ""
tmpDTMHNO = resROWS(trimax).ItemArray(20)
tmpDTMHNO = tmpDTMHNO.Remove(2, 8)

If tmpDTMHNO = "09" Then
'DELETE or REMOVE OPERATION

'Check the resROWS(i) to see if it is the
ItemArray(20)'s first 2
'characeter is "09". If ture than remove the row from
tmpTABLE.

'===========================================
tmpTABLE.Rows.Remove(resROWS(trimax))
'============================================

Else
'REPLACE OPERATION
'If the rows ItemArray(20)'s first 2 characeter is NOT
"09" then
'replace that rows ItemArray(16) value with variable
call myBALANCE
'and the replace the entire row in tmpTABLE

'===========================================
resROWS(trimax).ItemArray(16) = CStr(myBALANCE)
Dim rowE As DataRow = resROWS(trimax)
'Delete the rows first
tmpTABLE.Rows.Remove(resROWS(trimax))
'And insert the new row
tmpTABLE.Rows.Add(rowE)
'============================================
End If
Next
End If
Next

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


My problem lies in here:
'DELETE or REMOVE OPERATION
'===========================================
tmpTABLE.Rows.Remove(resROWS(trimax))
'============================================

And

'REPLACE OPERATION
'===========================================
resROWS(trimax).ItemArray(16) = CStr(myBALANCE)
Dim rowE As DataRow = resROWS(trimax)
'Delete the rows first
tmpTABLE.Rows.Remove(resROWS(trimax))
'And insert the new row
tmpTABLE.Rows.Add(rowE)
'============================================


I couldn't find time to test it yet, but I am wondering if the line
tmpTABLE.Rows.Remove(resROWS(trimax))

realy removes the data. Plus does the replace operation is works?
Because when I test it this part:

resROWS(trimax).ItemArray(16) = CStr(myBALANCE)

It seems I cannot replace the value in column(16).

What I realy need is (as you can see from my code) does this lines is
correct that removes the row from dataTable?
tmpTABLE.Rows.Remove(resROWS(trimax))

Plus does this code replace the value for specific column in specific rows:
resROWS(trimax).ItemArray(16) = CStr(myBALANCE)

I realy appricate if I can find some answer. By the way working with data
that is constructed in AS400 nearly 25 years ago and updated nearly 10 years
ago it realy kills me to code very efficently.

Anyway I thank you for your kind understanding to reading my post.

Rgds,
GC

 

[Guest]

Hi,
The not perfect way to achive this is in below. I will try to test the Cor's
pesude code on vb-tips.com

If tmpDTMHNO = "09" Then
'DELETE or REMOVE OPERATION
++++++++++++++++++++++++++++++++++++++++
resROWS(trimax).Delete()
Else
'REPLACE OPERATION
+++++++++++++++++++++++++++++++++++++++++++++++++
Dim mFlag As Boolean = False
resROWS(trimax).Item(16) = CStr(myBALANCE)
If resROWS(trimax).Item(16) = "0" Then
mFlag = True
Else
mFlag = False
End If

If mFlag = True Then
resROWS(trimax).Delete()
Else
'Do Nothing in here
End If
End If

Thank you.

Rgds,
Niyazi