Table.Compute Woes (URGENT)

  • Thread starter Thread starter scorpion53061
  • Start date Start date
S

scorpion53061

The code below is suppose to take data from a dataset and total it by
month.

What it is doing is best represented by a spreadsheet. It seems to be
completely ignoring the filter set down.

PLEASE look and suggest something....

http://www.kjmsolutions.com/ibou.ZIP

Dim foundrows As DataRow()
Dim r As DataRow
Dim rowcount As Integer
Dim strexpression As String
Dim alreadyexists As Boolean
Dim r1 As DataRow
Dim drnew1 As DataRow
Dim itemcheck1 As Integer

For itemcheck = 0 To dstrancopy.Tables(0).Rows.Count - 1
strexpression = "ITEM = '" &
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM") & "'"
drnew1 = listds.Tables(0).NewRow
drnew1.Item("ITEMNO") =
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM")
foundrows = dstrancopy.Tables(0).Select(strexpression)
MsgBox(foundrows.Length)
For Each r1 In foundrows
For itemcheck1 = 0 To listds.Tables(0).Rows.Count - 1

If listds.Tables(0).Rows(itemcheck1).Item("ITEMNO") =
r1.Item("ITEM") Then
alreadyexists = True
Exit For
Else
alreadyexists = False
End If
Next

If alreadyexists = False Then
drnew1.Item("JAN") = r.Table.Compute(r1.Item("UCOST") *
r1.Item("QTY"), "(TYPE <> 'CASH CREDIT' OR TYPE <> 'INVOICE CREDIT' OR TYPE
<> 'RGA') and (DATE = " &
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 2),
String) = "1/" Or
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 3),
String) = "01/")
drnew1.Item("JANQTY") = r1.Table.Compute(r1.Item("QTY"),
"(TYPE <> 'CASH CREDIT' OR TYPE <> 'INVOICE CREDIT' OR TYPE <> 'RGA') and
(DATE = " &
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 2),
String) = "1/" Or
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 3),
String) = "01/")
drnew1.Item("FEB") = r1.Table.Compute(r1.Item("UCOST") *
r1.Item("QTY"), "(TYPE <> 'CASH CREDIT' OR TYPE <> 'INVOICE CREDIT' OR TYPE
<> 'RGA') and (DATE = " &
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 2),
String) = "2/" Or
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 3),
String) = "02/")
drnew1.Item("FEBQTY") = r1.Table.Compute(r1.Item("QTY"),
"(TYPE <> 'CASH CREDIT' OR TYPE <> 'INVOICE CREDIT' OR TYPE <> 'RGA') and
(DATE = " &
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 2),
String) = "2/" Or
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 3),
String) = "02/")
listds.Tables(0).Rows.Add(drnew1)
End If
Next
Next
 
Scorp:

The documentation doesn't specifically address it, but I think you may have
a problem with Type as word. I had a field Line Count and even if I
escaped it, it would blow up on me. I'll be up pretty late tonight, so
change those two and let me know if that doesn't fix it. We'll figure it
out.
 
Actually, no, I tried it with Line Count and it still blew up. I changed
the columnname to Line_Count and that fixed my problem.
Another thing I was thinking....you may want to make a few expression
columns replacing the Left and Format's with Expression columns. Then, you
can just use the column names and it will probably be a little easier to
troubleshoot and maintain.

Scorp, just to see what' happening behind the scenes, but in some
Debug.WriteLines and let's see what the values are. Basically I just want
to confirm that it's the expression that's failing and if we see the
expression at each pass, that'll probably help out .
 
I'd write one after each compute statement to verify what value is being
returned. The more the merrier in this case b/c one of them will tip us off
to what's happening.
 
Bill,

I removed the TYPE statement entirely and the results were exactly the same
as before......

hmmmmm.......
drnew1.Item("JAN") = r1.Table.Compute(r1.Item("UCOST") * r1.Item("QTY"),
"(DATE = " &
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 2),
String) = "1/" Or
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 3),
String) = "01/")
Debug.WriteLine(r1.Table.Compute(r1.Item("QTY"), ""))
drnew1.Item("JANQTY") = r1.Table.Compute(r1.Item("QTY"),
"(DATE = " &
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 2),
String) = "1/" Or
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 3),
String) = "01/")
drnew1.Item("FEB") = r1.Table.Compute(r1.Item("UCOST") *
r1.Item("QTY"), "(DATE = " &
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 2),
String) = "2/" Or
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 3),
String) = "02/")
drnew1.Item("FEBQTY") = r1.Table.Compute(r1.Item("QTY"),
"(DATE = " &
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 2),
String) = "2/" Or
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 3),
String) = "02/")
 
Scorp:

That's telling. Try this :
drnew1.Item("JAN") = r1.Table.Compute(r1.Item("UCOST") * r1.Item("QTY"),
"Substring(Date, 0, 2) = "1/" Or Substring(Date, 0, 3) = "01/") . I'm just
wonding what the comparison value is. If it's not type, then either the
Date Expression is best suspect. I can't remember if Substring uses 0 or 1
for the beginnign position, but I'm guessing it's 0. I'm just wondering if
the expression isn't liking the other syntax, but I know Substring is a
legit expression value.
 
You are probably right.

I worked so dang hard on that date statemnet you would not believe.

Easy as pie for the vets I am sure.

Let you know here in a minute......
 
Additional information: Type mismatch in function argument: Substring(),
argument 1, expected System.String

drnew1.Item("JAN") = r1.Table.Compute(r1.Item("UCOST") * r1.Item("QTY"),
"Substring(" & r1.Item("Date") & ", 0, 2) = '1/' Or Substring(" &
r1.Item("Date") & ", 0, 3) = '01/'")
 
Yeah, that makes sense. Let me see what the conversions are.
scorpion53061 said:
Additional information: Type mismatch in function argument: Substring(),
argument 1, expected System.String

drnew1.Item("JAN") = r1.Table.Compute(r1.Item("UCOST") * r1.Item("QTY"),
"Substring(" & r1.Item("Date") & ", 0, 2) = '1/' Or Substring(" &
r1.Item("Date") & ", 0, 3) = '01/'")

or
Click to expand...
dstrancopy.Tables(0).Rows.Count -
 
Yeah, I'll be up for a while, but if not, I'll be around tomorrow morning.
scorpion53061 said:
Bill,

My wife is hollering at me....... :(

I will catch you later tonight ok?

or
Click to expand...
dstrancopy.Tables(0).Rows.Count -
 
Tried this but it was not finding 01 or 1/ using this method.
But it did return zeros so maybe there is hope with this method.

If foundrows.IndexOf(r1.ItemArray, "01") >= 0 Then
drnew1.Item("JAN") =
r1.Table.Compute(r1.Item("UCOST") * r1.Item("QTY"), "")
drnew1.Item("JANQTY") =
r1.Table.Compute(r1.Item("QTY"), "")
Else
drnew1.Item("JAN") = 0
drnew1.Item("JANQTY") = 0
End If
 
Scorp:

I was thinking, if you make an Expression column which just equals DATE, but
the type is System.String, then Substring should work.
 
Scorpion,
In addition to William's suggestions.

According to the help DataTable.Compute requires an aggregate function in
the expression column, it appears that you are sending in a scalar
(constant) values.
drnew1.Item("JAN") = r.Table.Compute(r1.Item("UCOST") *
r1.Item("QTY"), "(TYPE <> 'CASH CREDIT' OR TYPE <> 'INVOICE CREDIT' OR TYPE
<> 'RGA') and (DATE = " &
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 2),
String) = "1/" Or
CType(Microsoft.VisualBasic.Strings.Left(r1.Item("DATE".ToString), 3),
String) = "01/")
Click to expand...

In addition to Williams suggestions, I would make five suggestions.

1. Seperate the expression & filter from the Compute statement.
Dim expression As String = r1.Item("UCOST") * r1.Item("QTY")
Dim filter As String = "(TYPE <> 'CASH CREDIT' OR TYPE ...

drnew1.Item("JAN") = r.Table.Compute(expression, filter)

2. Use Option Strict On at the top of your listing.

Both of these should help identify where your problems are!

For example in the above expression you are computing a value, then passing
this constant for the expression, I would expect the expression to be
something like "Sum(TCost)" Where TCost is a computed column with an
expression of "UCOST * QTY" (See help for details).

3. Just to simplify your code you can use DataTable.Rows.Find to find the
item, if you add a Primary Key to your DataTable. This would eliminate the
innermost for loop.

4. Instead of (or in addition to) the DATE column, I would consider adding a
MONTH column that represented just the Month part of the Date when that row
was added to the dataset (I believe it will need to be computed when the row
is added). This would reduce your filter to "(TYPE <> 'CASH CREDIT' OR TYPE
<> 'INVOICE CREDIT' OR TYPE <> 'RGA') and (Month = " &
DirectCast(r1.Item("DATE"), Date).Month

5. Consider using the "In" operator instead of checking each type. "Not Type
In ('CASH CREDIT', 'INVOIC CREDIT', 'RGA')

The biggest thing is probably #1, if you split your expressions into smaller
components they would be easier to follow & probably easier to diagnos! (in
other words have 5 or 6 temporary variables in #1 to split each part of the
expression into smaller parts).


Hope this helps
Jay
 
Ok using your all suggestions I added a column to the original dataset this
is coming from (dstrancopy) called 'MONTH'.

It almost worked perfectly except it only added the first months totals.

It is like the loop is wrong.

Thank you both so much for your help!!

Jay, if you need a data sample to look at that I am seeing it is at

http://www.kjmsolutions.com/ibou.ZIP


For itemcheck = 0 To dstrancopy.Tables(0).Rows.Count - 1
strexpression = "ITEM = '" &
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM") & "'"
drnew1 = listds.Tables(0).NewRow
drnew1.Item("ITEMNO") =
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM")
foundrows = dstrancopy.Tables(0).Select(strexpression)
For Each r1 In foundrows
For itemcheck1 = 0 To listds.Tables(0).Rows.Count - 1

If listds.Tables(0).Rows(itemcheck1).Item("ITEMNO") =
r1.Item("ITEM") Then
alreadyexists = True
Exit For
Else
alreadyexists = False
End If
Next

If alreadyexists = False Then
If r1.Item("MONTH") = "Jan" Then
drnew1.Item("JAN") =
r1.Table.Compute(r1.Item("UCOST") * r1.Item("QTY"), r1.Item("MONTH") =
"Jan") 'CStr(r1.Item("DATE")).IndexOf("1/") > -1 Or
CStr(r1.Item("DATE")).IndexOf("01/") > -1)
drnew1.Item("JANQTY") =
r1.Table.Compute(r1.Item("QTY"), r1.Item("MONTH") = "Jan")
'CStr(r1.Item("DATE")).IndexOf("1/") > -1 Or
CStr(r1.Item("DATE")).IndexOf("01/") > -1)
Else
drnew1.Item("JAN") = 0
drnew1.Item("JANQTY") = 0
End If

If r1.Item("MONTH") = "Feb" Then
drnew1.Item("FEB") =
r1.Table.Compute(r1.Item("UCOST") * r1.Item("QTY"), r1.Item("MONTH") =
"Feb") 'CStr(r1.Item("DATE")).IndexOf("1/") > -1 Or
CStr(r1.Item("DATE")).IndexOf("01/") > -1)
drnew1.Item("FEBQTY") =
r1.Table.Compute(r1.Item("QTY"), r1.Item("MONTH") = "Feb")
'CStr(r1.Item("DATE")).IndexOf("1/") > -1 Or
CStr(r1.Item("DATE")).IndexOf("01/") > -1)
Else
drnew1.Item("FEB") = 0
drnew1.Item("FEBQTY") = 0
End If
listds.Tables(0).Rows.Add(drnew1)
End If
Next
Next
 
Scorpion,
The code I came up with is after my response (based on my suggestions):

For this code to work you need to make 2 changes:
1. the listds data set, needs to have a primary key of ITEMNO (column).
2. The dstrancopy table needs to have a MONTH column, with 3 character month
abbreviations.

It sounds like you are confusing what can be calculated by VB.NET within
your code, and what needs to be an expression to pass to the
DataTable.Compute filter & expression parameters.

The Scorpian53061 routine is effectively your routine, Hopefully my changes
did not actually change the algorithm too much ;-)

The CalculateTotals routine calculates the totals for a month. (the "meat"
of the problem).

Notice the filter checks for the requested month in the MONTH column of the
dstrancopy table.

Dim filter As String = String.Format("(Not TYPE in ('CASH CREDIT',
'INVOICE CREDIT', 'RGA')) and (MONTH = '{0}')", month)

While the Compute takes an expression that has an aggregate, so that values
are calculated on the columns in the dstrancopy table.

drnew1.Item(c1) = table.Compute("Sum(UCOST) * Sum(QTY)", filter)

My concern is that all items are added together in the listds, because you
have ITEMNO in listds, I have to wonder if the above filter should include
ITEM also.

Dim filter As String = String.Format("ITEM = '{0}' and (Not TYPE in
('CASH CREDIT', 'INVOICE CREDIT', 'RGA')) and (MONTH = '{1}')",
row.Item("ITEM"), month)

I would make 'row.Item("ITEM")' a parameter to CalculateTotals!

Hope this helps
Jay

Private Sub Scorpian53061()
For Each tranRow As DataRow In dstrancopy.Tables(0).Rows
Dim strexpression As String = String.Format("ITEM = '{0}'",
tranRow.Item("ITEM"))
Dim foundrows As DataRow() =
dstrancopy.Tables(0).Select(strexpression)
For Each row As DataRow In foundrows
If listds.Tables(0).Rows.Find(row.Item("ITEM")) Is Nothing
Then

Debug.WriteLine(tranRow.Item("ITEM"), "Item")
Debug.Indent()

Dim drnew1 As DataRow = listds.Tables(0).NewRow

drnew1.Item("ITEMNO") = row.Item("ITEM")

CalculateTotals(drnew1, row.Table, "JAN")
CalculateTotals(drnew1, row.Table, "FEB")
CalculateTotals(drnew1, row.Table, "MAR")
CalculateTotals(drnew1, row.Table, "APR")
CalculateTotals(drnew1, row.Table, "MAY")
CalculateTotals(drnew1, row.Table, "JUN")
CalculateTotals(drnew1, row.Table, "JUL")
CalculateTotals(drnew1, row.Table, "AUG")
CalculateTotals(drnew1, row.Table, "SEP")
CalculateTotals(drnew1, row.Table, "OCT")
CalculateTotals(drnew1, row.Table, "NOV")
CalculateTotals(drnew1, row.Table, "DEC")

listds.Tables(0).Rows.Add(drnew1)
Debug.Unindent()
End If
Next
Next

End Sub

Private Sub CalculateTotals(ByVal drnew1 As DataRow, ByVal table As
DataTable, ByVal month As String)
Dim c1 As String = month
Dim c2 As String = month & "QTY"

Dim filter As String = String.Format("(Not TYPE in ('CASH CREDIT',
'INVOICE CREDIT', 'RGA')) and (MONTH = '{0}')", month)

drnew1.Item(c1) = table.Compute("Sum(UCOST) * Sum(QTY)", filter)
Debug.WriteLine(drnew1.Item(c1), c1)

drnew1.Item(c2) = table.Compute("Sum(QTY)", filter)
Debug.WriteLine(drnew1.Item(c2), c2)

End Sub

Hope this helps
Jay
 
Hi Jay,

While you were working I was working as well.

This is what I did that has now worked except the empty columns that had no
transactions present were empty when they were suppose to have zero. I added
the MONTH column that seems to have made the select statement work much
better.

Yours looks a lot better than mine so I am oging to wipe it out and do
yours.....

Check back in a few

For itemcheck = 0 To dstrancopy.Tables(0).Rows.Count - 1
For itemcheck1 = 0 To listds.Tables(0).Rows.Count - 1
If listds.Tables(0).Rows(itemcheck1).Item("ITEMNO") =
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM") Then
alreadyexists = True
Exit For
Else
alreadyexists = False
End If
Next
If alreadyexists = False Then
drnew1 = listds.Tables(0).NewRow
drnew1.Item("ITEMNO") =
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM")
strexpression = "ITEM = '" &
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM") & "' and MONTH = 'Jan'"
foundrows = dstrancopy.Tables(0).Select(strexpression)
For Each r In foundrows
If r.Table.Compute(r.Item("UCOST") * r.Item("QTY"),
r.Item("MONTH") = "Jan") Is Nothing Then
drnew1.Item("JAN") = 0
drnew1.Item("JANQTY") = 0
Else
drnew1.Item("JAN") = r.Table.Compute(r.Item("UCOST")
* r.Item("QTY"), r.Item("MONTH") = "Jan")
drnew1.Item("JANQTY") =
r.Table.Compute(r.Item("QTY"), r.Item("MONTH") = "Jan")
End If
Next
strexpression = "ITEM = '" &
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM") & "' and MONTH = 'Feb'"
foundrows = dstrancopy.Tables(0).Select(strexpression)
For Each r In foundrows
If r.Table.Compute(r.Item("UCOST") * r.Item("QTY"),
r.Item("MONTH") = "Feb") Is Nothing Then
drnew1.Item("FEB") = 0
drnew1.Item("FEBQTY") = 0
Else
drnew1.Item("FEB") = r.Table.Compute(r.Item("UCOST")
* r.Item("QTY"), r.Item("MONTH") = "FEB")
'CStr(r.Item("DATE")).IndexOf("1/") > -1 Or
CStr(r.Item("DATE")).IndexOf("01/") > -1)
drnew1.Item("FEBQTY") =
r.Table.Compute(r.Item("QTY"), r.Item("MONTH") = "FEB")
'CStr(r.Item("DATE")).IndexOf("1/") > -1 Or
CStr(r.Item("DATE")).IndexOf("01/") > -1)
End If
Next
listds.Tables(0).Rows.Add(drnew1)
End If


Next
 
Jay and Bill,

Thank you very much for your help....

There was a lot of blood on the floor (mostly mine) on this one so to
speak....

Jay I am working with your solution.....I am running into some problems but
since this is working I need to get some reports done with it.

Again thank you very much and if you see anyting here to be alarmed about
please holler.....

For itemcheck = 0 To dstrancopy.Tables(0).Rows.Count - 1
For itemcheck1 = 0 To listds.Tables(0).Rows.Count - 1
If listds.Tables(0).Rows(itemcheck1).Item("ITEMNO") =
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM") Then
alreadyexists = True
Exit For
Else
alreadyexists = False
End If
Next
If alreadyexists = False Then
'Dim filter As String = String.Format("(Not TYPE in ('CASH
CREDIT','INVOICE CREDIT', 'RGA')) and (MONTH = '{0}')",
Month(r.Item("DATE")))
drnew1 = listds.Tables(0).NewRow
drnew1.Item("ITEMNO") =
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM")

strexpression = "ITEM = '" &
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM") & "' and MONTH = 'Jan' and
((TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'CASH CREDIT') OR (TYPE <> 'RGA'))"
foundrows = dstrancopy.Tables(0).Select(strexpression)
If foundrows.Length = 0 Then
drnew1.Item("JAN") = 0
drnew1.Item("JANQTY") = 0
Else
For Each r In foundrows
drnew1.Item("JAN") = r.Table.Compute(r.Item("UCOST")
* r.Item("QTY"), r.Item("MONTH") = "Jan")
drnew1.Item("JANQTY") =
r.Table.Compute(r.Item("QTY"), r.Item("MONTH") = "Jan")
Next
End If

strexpression = "ITEM = '" &
dstrancopy.Tables(0).Rows(itemcheck).Item("ITEM") & "' and MONTH = 'Feb' and
((TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'CASH CREDIT') OR (TYPE <> 'RGA'))"
foundrows = dstrancopy.Tables(0).Select(strexpression)
If foundrows.Length = 0 Then
drnew1.Item("FEB") = 0
drnew1.Item("FEBQTY") = 0
Else
For Each r In foundrows
drnew1.Item("FEB") = r.Table.Compute(r.Item("UCOST")
* r.Item("QTY"), r.Item("MONTH") = "FEB")
'CStr(r.Item("DATE")).IndexOf("1/") > -1 Or
CStr(r.Item("DATE")).IndexOf("01/") > -1)
drnew1.Item("FEBQTY") =
r.Table.Compute(r.Item("QTY"), r.Item("MONTH") = "FEB")
'CStr(r.Item("DATE")).IndexOf("1/") > -1 Or
CStr(r.Item("DATE")).IndexOf("01/") > -1)
Next
End If

listds.Tables(0).Rows.Add(drnew1)
End If


Next
 
Back
Top