Compare data of the last day with the past 4 day’s data

Dobrobit

Can some one help Listbox1 is Data A,B,C,D,E from the SQL base . Id automatically increases every day after entering new values for A,B,C,D,E while Id is date Every day I want to compare data of the last day with the past 4 day’s data and get values of P1, P2, P3, P4. Listbox1 =Data from base Id A B C D E 15 2 4 6 7 8 14 2 6 8 7 2 13 1 4 1 5 8 12 3 3 3 7 2 11 5 6 8 4 1 10 5 8 7 7 7 09 4 4 7 7 7 08 1 2 6 5 4 07 1 5 8 9 5 06 6 6 3 3 2 05 2 2 5 7 8 04 4 7 5 2 3 03 4 6 8 4 9 02 2 2 4 4 7 01 5 7 5 6 1 Listbox2 = Expected results with VB codes Id P1 P2 P3 P4 15 2 2 1 0 14 0 2 2 0 13 0 0 0 1 12 0 1 1 1 11 1 0 0 1 10 3 0 0 0 09 0 0 0 1 08 1 0 2 0 07 0 0 0 1 06 0 0 1 0 05 1 0 2 1 04 1 0 2 - 03 1 0 - - 02 0 - - - 01 - - - - The problem is that I want through VB codes to add these values to be added to listbox2 Here P1 at Id 15 = 2 is the value got counting when Data A at Id 15 = data A at Id = 14 + Data B at Id 15 = data B at Id = 14 + Data C at Id 15 = data C at Id = 14 + Data D at Id 15 = data D at Id = 14 + Data E at Id 15 = data D at Id = 14 (P1 = 2 Data A & D) Here P2= 2 is the value got counting when Data A at Id 15 = data A at Id = 13 + Data B at Id 15 = data B at Id = 13 + Data C at Id 15 = data C at Id = 13 + Data D at Id 15 = data D at Id = 13 + Data E at Id 15 = data D at Id = 13 (P2 = 2 Data B & E) Here P3= 1 is the value got counting when Data A at Id 15 = data A at Id = 12 + Data B at Id 15 = data B at Id = 12 + Data C at Id 15 = data C at Id = 12 + Data D at Id 15 = data D at Id = 12 + Data E at Id 15 = data D at Id = 12 (P3 = 1 Data D) Here P4= 0 is the value got counting when Data A at Id 15 = data A at Id = 11 + Data B at Id 15 = data B at Id = 11 + Data C at Id 15 = data C at Id = 11 + Data D at Id 15 = data D at Id = 11 + Data E at Id 15 = data D at Id = 11 (P4 = :) 0 Data) Etc….etc Even the user wants to compare up to the past 15 data so that I have up to P15

Jumping Jupiter

What version of visual basic are you using VB6, VB.net or another? The code will be slightly different in the way you access the listbox depending on the version.

Dobrobit

Thanks for your reply Iam using MS VB 2008 EXPRESS EDITION jaakinye

Dobrobit

Re: Compare data of the last day with the past 4 day’s I use VB 2008 EXPRESS EDITION I dont have formular as you can see from this that is why I need VB eihter throuh array or index to get this Id A B C D E 15 2 4 6 7 8 14 2 6 8 7 2 for example as above,data A in day 15 repeats its value of day 14 and data D in da 15 also repeats its value of day 14. These are 2 data that repeat their values of day 14.Data A and D are 2 data therefore P1 = 2. Id A B C D E 15 2 4 6 7 8 13 1 4 1 5 8 The same thing above P2 (day 15 compared with day 13. Data B in day 15 repeats its value of day 13 and data E at day 15 also repeats its value of day 13.This gives 2 data and therefore P2 = 2 Id A B C D E 15 2 4 6 7 8 12 3 3 3 7 2 As above only 1 data (D) at day 15 repeats its value on day 12.Therefore P3 = 1 Id A B C D E 15 2 4 6 7 8 11 5 6 8 4 1 as above no data at day 15 repeats its value at day 11.Therefore P4 = 0. Day 15 is the last data and is compared with day 14,13,12and 11.The next day will be day 16 and it will be copared with days 15,14,13and 12 while day 17 will be compared with days 16,15,14 and 13 etc. How do I use codes to get these? Thanks for your help

Jumping Jupiter

I assume that your are quite new to VB.net but I may be wrong. I have therfore written the attached code using an array as you asked. I would not say it is the best method but if you are learning they I thought it better. Create a new project with a form1 and two listboxes (ListBox1 and ListBox2) Replace the code from your form with the attached code. It automatically generates test data to show you it working. It may not be exactly what you want but its a start.

Public Class Form1

Const NumberOfDaystocountBack = 4 'This is a constant for the number of dats you will count back
Dim OriginalDataArray(15, 5) As String 'This is a sample array with 15 records each with 5 pieces of data
Dim ResultsArray(1, 1) As String 'This is a sample array that will store the data, it is resized with the code

Private Sub Main()
'Generate test data
Dim X As Integer
Dim Y As Integer
Dim TempString As String
For X = 15 To 0 Step -1
TempString = ""
For Y = 0 To 4
OriginalDataArray(X, Y) = Int(Rnd() * 10)
TempString = TempString & OriginalDataArray(X, Y).ToString & ", "
Next
ListBox1.Items.Add(TempString)
Next

GenerateResults()

For X = 15 To 0 Step -1
  TempString = ""
  For Y = 0 To 3

    TempString = TempString & ResultsArray(X, Y).ToString & ", "
  Next
  ListBox2.Items.Add(TempString)
Next

End Sub

Private Sub GenerateResults()
Dim MaxDayCount As Long
Dim LoopCounter As Integer
Dim RecordLoopCounter As Long
Dim DataLoopCounter As Long
Dim DataCount As Integer 'count of the number of data elements

Dim TempArray1() As Integer
Dim TempArray2() As Integer

DataCount = OriginalDataArray.GetUpperBound(1) 'This will be '5' for the sample array given
MaxDayCount = OriginalDataArray.GetUpperBound(0)  'This would be 50 for the sample array given

ReDim ResultsArray(MaxDayCount, NumberOfDaystocountBack) 'Resize the results array to accept data


For RecordLoopCounter = MaxDayCount To 0 Step -1 'Steps back through records of data

  'Pass the two days data to the comparison function
  For DataLoopCounter = 1 To NumberOfDaystocountBack

    ReDim TempArray1(DataCount) 'Resize array to the number of data elements and clears data
    ReDim TempArray2(DataCount) 'Resize array to the number of data elements and clears data
    If RecordLoopCounter - DataLoopCounter >= 0 Then