Re: Table.Compute Woes (URGENT)

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: scorpion53061 (scorpion_53061_at_nospamhereeveryahoo.com)
Date: 03/21/04 

Date: Sun, 21 Mar 2004 11:53:50 -0600

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

"Jay B. Harlow [MVP - Outlook]" <Jay_Harlow_MVP@msn.com> wrote in message
news:uQXDQU2DEHA.1128@TK2MSFTNGP11.phx.gbl...
> 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/")
>
> 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
>
> "scorpion53061" <scorpion_53061@nospamhereeveryahoo.com> wrote in message
> news:uIdWOAuDEHA.1544@TK2MSFTNGP09.phx.gbl...
> > 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 spread***. 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
> >
> >
>
>

Quantcast