Went Backwards....

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

scorpion53061

I really messed this up now. ....it is not picking up any rows though it is
suppose to have.


Do
i = i + 1
If i = dstrancopy.Tables(0).Rows.Count - 1 Then
Exit Do
End If
Dim strexpression As String = "ITEM = '" &
dstrancopy.Tables(0).Rows(i + 1).Item("ITEM") & "'"
Dim foundrows As DataRow() =
dstrancopy.Tables(0).Select(strexpression, "DESC", DataViewRowState.Added)
For Each r In foundrows
For itemcheck = 0 To listds.Tables(0).Rows.Count - 1
If listds.Tables(0).Rows(itemcheck).Item("ITEMNO") =
r.Item("ITEM") Then
thisitemisalreadyin = True
Exit For
End If
Next
If thisitemisalreadyin = True Then
Exit For
End If
drnew1 = listds.Tables(0).NewRow
drnew1.Item("ITEMNO") = r.Item("ITEMNO")
For Each c In r.Table.Columns
If Format(r.Item("DATE"), "MMM") = "JAN" Then
January = 0
If LTrim(RTrim(r.Item("UM"))) = "E" Or
LTrim(RTrim(r.Item("UM"))) = "EA" Or LTrim(RTrim(r.Item("UM"))) = "NK" Then
tempamount = Format(r.Item("UCOST") *
r.Item("qty"), "##0.##")
End If
If LTrim(RTrim(r.Item("UM"))) = "C" Then
tempamount = Format(r.Item("UCOST") *
(r.Item("qty") / 100), "##0.##")
End If
If LTrim(RTrim(r("UM"))) = "M" Then
tempamount = Format(r.Item("UCOST") *
(r.Item("qty") / 1000), "##0.##")
End If
January = January + tempamount
If tempamount = 0 Or tempamount = Nothing Then
drnew1.Item("JAN") = 0
Else
drnew1.Item("JAN") = January
End If
End If
listds.Tables(0).Rows.Add(drnew1)
'next column
Next c
Next

Loop
 
Scorp, I lost you on this totally. If you need group summaries, use
DataTable.Compute and just call it a few times for each condition. If you
need totals and subtotals, it can be done totally with DataTable.Compute and
expression columns. At the simplest form, ignoring formatting, can you tell
me the column you are trying to aggregate and on what change condition?
 
Sure......

In dataset trans1 I have columns called

ITEMNO
ITEMCOST
DATEOFTRANS

what we want to do is take all ITEMNO that are the same and add their costs
together for each month of the year.

I am trying to use the computer statement you are talking about. I have
never had a need to use it up till now so it is going to take a bit.
 
This is what I got when I tried to use the compute statement.....I am sure I
am not using it right but you see my mistake here...

r.Item("DATE") = (Format(r.Item("DATE"),"MMM" = "Jan" is the filter I am
trying to do.


An unhandled exception of type 'System.InvalidCastException' occurred in
microsoft.visualbasic.dll

Additional information: Cast from string "Fal39e" to type 'Date' is not
valid.


drnew1.Item("JAN") = r.Table.Compute(r.Item("UCOST") * r.Item("QTY"),
r.Item("DATE") = (Format(r.Item("DATE"), "MMM" = "Jan")))
 
revised.......I forgot to include credit issues......

I haven't tested yet but pretty sure I made a mistake somewhere........

drnew1.Item("JAN") = (r.Table.Compute((r.Item("UCOST") * r.Item("QTY")),
"DATE = " & Format(r.Item("DATE"), "MMM") = "Jan") -
(r.Table.Compute(r.Item("UCOST") * r.Item("QTY"), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Jan") & " and " & r.Item("TYPE") <>
"INVOICE") & " Or " & r.Item("TYPE") <> "PACK & HOLD") '
(r.Table.Compute(r.Item("TYPE") * r.Item("QTY"), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Jan")) & r.Item("TYPE") = "INVOICE
CREDIT"), "DATE = " & Format(r.Item("DATE"), "MMM") = "Jan")
 
This appears to be giving relevant data although I am having trouble finding
a way to verify it.......Do you think this will work?.

drnew1.Item("JAN") = Format(r.Table.Compute((r.Item("UCOST") *
r.Item("QTY")), "DATE = " & Format(r.Item("DATE"), "MMM") = "Jan" & " and
((TYPE <> 'CASH CREDIT') OR (TYPE <> 'INVOICE CREDIT') OR (TYPE <>
'RGA'))"), "##0.##")
 
Bill,

This statement above seems to produce the same value in each month with this
code....

For Each c In r.Table.Columns
If LTrim(RTrim(r.Item("UM"))) = "E" Or
LTrim(RTrim(r.Item("UM"))) = "EA" Or LTrim(RTrim(r.Item("UM"))) = "NK" Then
drnew1.Item("JAN") =
Format(r.Table.Compute((r.Item("UCOST") * r.Item("QTY")), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Jan" & " and ((TYPE <> 'CASH CREDIT') OR
(TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'RGA'))"), "##0.##0")
drnew1.Item("FEB") =
Format(r.Table.Compute((r.Item("UCOST") * r.Item("QTY")), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Feb" & " and ((TYPE <> 'CASH CREDIT') OR
(TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'RGA'))"), "##0.##0")
drnew1.Item("MAR") =
Format(r.Table.Compute((r.Item("UCOST") * r.Item("QTY")), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Mar" & " and ((TYPE <> 'CASH CREDIT') OR
(TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'RGA'))"), "##0.##0")
drnew1.Item("APR") =
Format(r.Table.Compute((r.Item("UCOST") * r.Item("QTY")), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Apr" & " and ((TYPE <> 'CASH CREDIT') OR
(TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'RGA'))"), "##0.##0")
drnew1.Item("MAY") =
Format(r.Table.Compute((r.Item("UCOST") * r.Item("QTY")), "DATE = " &
Format(r.Item("DATE"), "MMM") = "May" & " and ((TYPE <> 'CASH CREDIT') OR
(TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'RGA'))"), "##0.##0")
drnew1.Item("JUN") =
Format(r.Table.Compute((r.Item("UCOST") * r.Item("QTY")), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Jun" & " and ((TYPE <> 'CASH CREDIT') OR
(TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'RGA'))"), "##0.##0")
drnew1.Item("JUL") =
Format(r.Table.Compute((r.Item("UCOST") * r.Item("QTY")), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Jul" & " and ((TYPE <> 'CASH CREDIT') OR
(TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'RGA'))"), "##0.##0")
drnew1.Item("AUG") =
Format(r.Table.Compute((r.Item("UCOST") * r.Item("QTY")), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Aug" & " and ((TYPE <> 'CASH CREDIT') OR
(TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'RGA'))"), "##0.##0")
drnew1.Item("SEP") =
Format(r.Table.Compute((r.Item("UCOST") * r.Item("QTY")), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Sep" & " and ((TYPE <> 'CASH CREDIT') OR
(TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'RGA'))"), "##0.##0")
drnew1.Item("OCT") =
Format(r.Table.Compute((r.Item("UCOST") * r.Item("QTY")), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Oct" & " and ((TYPE <> 'CASH CREDIT') OR
(TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'RGA'))"), "##0.##0")
drnew1.Item("NOV") =
Format(r.Table.Compute((r.Item("UCOST") * r.Item("QTY")), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Nov" & " and ((TYPE <> 'CASH CREDIT') OR
(TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'RGA'))"), "##0.##0")
drnew1.Item("DEM") =
Format(r.Table.Compute((r.Item("UCOST") * r.Item("QTY")), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Dec" & " and ((TYPE <> 'CASH CREDIT') OR
(TYPE <> 'INVOICE CREDIT') OR (TYPE <> 'RGA'))"), "##0.##0")
End If
If LTrim(RTrim(r.Item("UM"))) = "C" Then
'drnew1.Item("JAN") =
(r.Table.Compute((r.Item("UCOST") * r.Item("QTY")), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Jan") - (r.Table.Compute(r.Item("UCOST") *
r.Item("QTY"), "DATE = " & Format(r.Item("DATE"), "MMM") = "Jan") & " and "
& r.Item("TYPE") = "INVOICE") & " Or " & r.Item("TYPE") <> "PACK & HOLD") '
(r.Table.Compute(r.Item("TYPE") * r.Item("QTY"), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Jan")) & r.Item("TYPE") = "INVOICE
CREDIT"), "DATE = " & Format(r.Item("DATE"), "MMM") = "Jan")
'MsgBox(drnew1.Item("JAN"))
End If
If LTrim(RTrim(r("UM"))) = "M" Then
'drnew1.Item("JAN") =
r.Table.Compute(r.Item("UCOST") * r.Item("QTY"), "DATE = " &
Format(r.Item("DATE"), "MMM") = "Jan")
End If
Next
listds.Tables(0).Rows.Add(drnew1)
End If
Next
Next
 
Scorp:

If you get rid of the format statements for a second, what happens? It's
kind of hard to tell from this b/c kof the formatters.
I don't see an aggregate anywhere and that's where these really come into
play. If you are just trying to multiply two fields, an expresson would
work for you.
The reason I suggest getting rid of the formatter in the expression is that
the data is in the columns already so you can just check against it
directly. If you wanted to see if some datavalue in Column Date =
'01/02/2003' then you wouldn't need to format it and check against that.
There are a good amount of functions that you can use to strip out times for
instace or most of what I can imagine you'd need.

What is the MMM?

Also, here's typically what an expression would look like:

DataTable.Compute("UCostColumn * UQuantityColumn ", "Date = Jan")

Compute works essentially as an aggregator where the second argument sets
the filter

I think the looping through everything might be causing the problem if I'm
reading this correctly.

Instead of looping through the rows, you could just use the compute function
and change the filter where you'd change things in your loop.
 
Scorp

Take a look at this code :

da.Fill(dt)

Dim UniqueVals As ArrayList = GetUniqueValues()

For x As Integer = 0 To UniqueVals.Count - 1

Debug.WriteLine(UniqueVals(x).ToString & ": " & dt.Compute("SUM(ItemCost)",
"ItemCost > -1 AND ItemNo =" & CType(UniqueVals(x), Integer)).ToString)

Next

End Sub

Private Function GetUniqueValues() As ArrayList

Dim dv As DataView = dt.DefaultView

dv.Sort = "ItemNo"

Dim i As Integer = -1

Dim UniqueList As New ArrayList

Dim viewCounter As IEnumerator = dv.GetEnumerator

Dim drv As DataRowView

While viewCounter.MoveNext

drv = CType(viewCounter.Current, DataRowView)

If CType(drv(0), Integer) <> i Then

UniqueList.Add(CType(drv(0), Integer))

i = CType(drv(0), Integer)

End If

End While

Return UniqueList

End Function



Here's the data from the table I used:

1 1 0 1/1/2004
1 5 1/1/2004
1 1 2 1/1/2004
1 2 0 1/1/2004
1 50 2/1/2004
1 100 2/1/2004
2 10 1/1/2004
2 5 1/1/2004
2 2 1/10/2004
2 800 3/1/2004
3 500 1/1/2004
3 100 1/1/2004
3 200 4/4/2004

Here's my output:

1: 197.0000

2: 817.0000

3: 800.0000



Is this essentially what you needed?
 
PS I used the same columnames and positions as you originally referred to
ItemNo, ItemCost, DateOfTrans
 
Hi Bill,

Problem solved......probably not the most elegant but....

If itemcheck2 = dstrancopy.Tables(0).Rows.Count Then
Exit Do
End If
Dim strexpression As String = "ITEM = '" &
dstrancopy.Tables(0).Rows(itemcheck2).Item("ITEM") & "'"
Dim foundrows As DataRow() =
dstrancopy.Tables(0).Select(strexpression, "DESC", DataViewRowState.Added)
Dim drnew1 As DataRow
For Each r In foundrows 'foundrows
Dim itemcheck1 As Integer
For itemcheck1 = 0 To listds.Tables(0).Rows.Count - 1
If listds.Tables(0).Rows(itemcheck1).Item("ITEMNO") =
r.Item("ITEM") Then
beenthere = True
Exit For
Else
beenthere = False
End If
Next
If beenthere = False Then
drnew1 = listds.Tables(0).NewRow
'If LTrim(RTrim(r.Item("UM"))) = "E" Or
LTrim(RTrim(r.Item("UM"))) = "EA" Or LTrim(RTrim(r.Item("UM"))) = "NK" Then
If Format(r.Item("DATE"), "MMM") = "Jan" Then
January = r.Table.Compute(r.Item("UCOST") *
r.Item("QTY"), Format(r.Item("DATE"), "MMM") = "Jan" & " AND TYPE <> 'CASH
CREDIT' OR TYPE <> 'INVOICE CREDIT' OR TYPE <> 'RGA'")
JanQty = r.Table.Compute(r.Item("QTY"),
Format(r.Item("DATE"), "MMM") = "Jan" & " AND TYPE <> 'CASH CREDIT' OR TYPE
<> 'INVOICE CREDIT' OR TYPE <> 'RGA'")
End If


'more months using the same process


TotalQty = JanQty + FebQty + MarQty + AprQty + MayQty + JunQty + JulQty +
AugQty + SeptQty + OctQty + NovQty + DecQty
TotalAmount = January + February + March + April + May +
June + July + August + September + October + November + December
drnew1.Item("ITEMNO") =
dstrancopy.Tables(0).Rows(itemcheck2).Item("ITEM")
drnew1.Item("JAN") = January
'r.Table.Compute(r.Item("UCOST") * r.Item("QTY"), Format(r.Item("DATE"),
"MMM") = "Jan" & " AND TYPE <> 'CASH CREDIT' OR TYPE <> 'INVOICE CREDIT' OR
TYPE <> 'RGA'")
drnew1.Item("JANQTY") = JanQty

'adding more rows for other months

drnew1.Item("TOTAL") = TotalAmount
drnew1.Item("TOTALQTY") = TotalQty
listds.Tables(0).Rows.Add(drnew1)
End If
Next
Loop
 
Glad you got it fixed. As far as the house, they finally got everythign
fixed last week. What a pain!

Cheers,

Bill
 
Back
Top