Decision Table and Tree (1 Viewer)

B

bradyjoosse

New Member
Joined
Mar 15, 2022
Messages
27
Gender
Male
HSC
2023
Hey,

I've currently got to do a decision tree and decision table for my decision support system assesment task, as well as this knowledge being important for the HSC, I am kind of lost on how both work. My visual basic macro code is below which takes inputs from form elements in excel then filters and displays data, I really don't understand what to do as I have looked at a bunch of past examples and videos and still don't understand it.


Rich (BB code): 
Sub FilterButton()
    Dim wsRawData As Worksheet
    Dim wsFilter As Worksheet
    Dim wsOutput As Worksheet
    Dim rngRawData As Range
    Dim rngFilter As Range
    Dim rngOutput As Range
    Dim criteriaRange As Range
    Dim Budget As Single
    Dim DownloadSpeed As Integer
    Dim UploadSpeed As Integer
    Dim Provider As String
    Application.ScreenUpdating = False
    
    ' Set references to the relevant worksheets
    Set wsRawData = ThisWorkbook.Worksheets("Raw Data")
    Set wsFilter = ThisWorkbook.Worksheets("NewHomeandInput")
    Set wsOutput = ThisWorkbook.Worksheets("Filtered Data")
    
    ' Set the range to be filtered (Raw Data range)
    Set rngRawData = wsRawData.Range("A2:I99")
    
    ' Set the range where filtered data will be outputted (Output Sheet)
    Set rngOutput = wsOutput.Range("A2")
    
    ' Get the filter criteria from the Filter Sheet
    Budget = wsFilter.Range("V14").Value
    DownloadSpeed = wsFilter.Range("V15").Value
    UploadSpeed = wsFilter.Range("V16").Value
    Provider = wsFilter.Range("V18").Value
    
    
    'Clear previous filtered data before importing the new data
    Sheets("Filtered Data").Select
    Range("A2:H99").Select
    Selection.ClearContents
    
    ' Clear any previous filters in the Raw Data range
    wsRawData.AutoFilterMode = False
    
    ' Apply the filter based on the criteria in the Filter Sheet
    With rngRawData
        .AutoFilter Field:=5, Criteria1:="<=" & DownloadSpeed ' Column F
        .AutoFilter Field:=6, Criteria1:="<=" & UploadSpeed ' Column G
        .AutoFilter Field:=8, Criteria1:="<=" & Budget ' Column I
        If Provider <> "Any" Then
            .AutoFilter Field:=2, Criteria1:=Provider ' Column B
        End If
    End With
    
    ' Copy the visible cells (filtered data) to the Output Sheet, including the header row
    rngRawData.SpecialCells(xlCellTypeVisible).Copy Destination:=rngOutput
    
    
    ActiveWorkbook.Worksheets("Filtered Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Filtered Data").Sort.SortFields.Add2 Key:=Range( _
        "E2:E42"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Filtered Data").Sort.SortFields.Add2 Key:=Range( _
        "G2:G42"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Filtered Data").Sort.SortFields.Add2 Key:=Range( _
        "F2:F42"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Filtered Data").Sort
        .SetRange Range("A1:H42")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Sheets("NewHomeandInput").Select
    Range("J2").Select
    
    ' Turn off the AutoFilter
    wsRawData.AutoFilterMode = False
    
    ' Clear the clipboard
    Application.CutCopyMode = False
    
    ' Autofit columns after pasting data
    Application.ScreenUpdating = True
End Sub
 
C

cossine

Well-Known Member
Joined
Jul 24, 2020
Messages
627
Gender
Male
HSC
2017
bradyjoosse said:
Hey,

I've currently got to do a decision tree and decision table for my decision support system assesment task, as well as this knowledge being important for the HSC, I am kind of lost on how both work. My visual basic macro code is below which takes inputs from form elements in excel then filters and displays data, I really don't understand what to do as I have looked at a bunch of past examples and videos and still don't understand it.


Rich (BB code): 
Sub FilterButton()
    Dim wsRawData As Worksheet
    Dim wsFilter As Worksheet
    Dim wsOutput As Worksheet
    Dim rngRawData As Range
    Dim rngFilter As Range
    Dim rngOutput As Range
    Dim criteriaRange As Range
    Dim Budget As Single
    Dim DownloadSpeed As Integer
    Dim UploadSpeed As Integer
    Dim Provider As String
    Application.ScreenUpdating = False
   
    ' Set references to the relevant worksheets
    Set wsRawData = ThisWorkbook.Worksheets("Raw Data")
    Set wsFilter = ThisWorkbook.Worksheets("NewHomeandInput")
    Set wsOutput = ThisWorkbook.Worksheets("Filtered Data")
   
    ' Set the range to be filtered (Raw Data range)
    Set rngRawData = wsRawData.Range("A2:I99")
   
    ' Set the range where filtered data will be outputted (Output Sheet)
    Set rngOutput = wsOutput.Range("A2")
   
    ' Get the filter criteria from the Filter Sheet
    Budget = wsFilter.Range("V14").Value
    DownloadSpeed = wsFilter.Range("V15").Value
    UploadSpeed = wsFilter.Range("V16").Value
    Provider = wsFilter.Range("V18").Value
   
   
    'Clear previous filtered data before importing the new data
    Sheets("Filtered Data").Select
    Range("A2:H99").Select
    Selection.ClearContents
   
    ' Clear any previous filters in the Raw Data range
    wsRawData.AutoFilterMode = False
   
    ' Apply the filter based on the criteria in the Filter Sheet
    With rngRawData
        .AutoFilter Field:=5, Criteria1:="<=" & DownloadSpeed ' Column F
        .AutoFilter Field:=6, Criteria1:="<=" & UploadSpeed ' Column G
        .AutoFilter Field:=8, Criteria1:="<=" & Budget ' Column I
        If Provider <> "Any" Then
            .AutoFilter Field:=2, Criteria1:=Provider ' Column B
        End If
    End With
   
    ' Copy the visible cells (filtered data) to the Output Sheet, including the header row
    rngRawData.SpecialCells(xlCellTypeVisible).Copy Destination:=rngOutput
   
   
    ActiveWorkbook.Worksheets("Filtered Data").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Filtered Data").Sort.SortFields.Add2 Key:=Range( _
        "E2:E42"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Filtered Data").Sort.SortFields.Add2 Key:=Range( _
        "G2:G42"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("Filtered Data").Sort.SortFields.Add2 Key:=Range( _
        "F2:F42"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Filtered Data").Sort
        .SetRange Range("A1:H42")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
   
    Sheets("NewHomeandInput").Select
    Range("J2").Select
   
    ' Turn off the AutoFilter
    wsRawData.AutoFilterMode = False
   
    ' Clear the clipboard
    Application.CutCopyMode = False
   
    ' Autofit columns after pasting data
    Application.ScreenUpdating = True
End Sub
Click to expand...
So if I am understanding correctly you need create a decision tree i.e. machine learning algorithm. Probably the best way to do this would be to use Python. The main packages you will have to use will be pandas (to read the excel file) and scikit-learn (create a decision tree). You will find many step-by-step guides on medium or towardsdatascience (websites) how to create a decision tree in a few lines of code.

For your IDE(the place you will write code). It is best to use Jupyter Notebook. To do that you will need download and run Anaconda. How to exactly run jupyter notebook will depend on your operating system. It is best to just watch a YouTube video on how to set uo jupyter notebook.

All the best,
cossine
 
D

dav53521

Well-Known Member
Joined
Mar 23, 2022
Messages
317
Gender
Male
HSC
2022
bradyjoosse said:
Hey,

I've currently got to do a decision tree and decision table for my decision support system assesment task, as well as this knowledge being important for the HSC, I am kind of lost on how both work. My visual basic macro code is below which takes inputs from form elements in excel then filters and displays data, I really don't understand what to do as I have looked at a bunch of past examples and videos and still don't understand it.
Click to expand...
A decision tree/table in IPT are a visual representation of what action should occur if certain conditions are met and a decision tree and table are just different ways of representing it.

A decision table is a table that has two main columns one of the being the rules (yes or no to each condition/action) and the conditions that represent certain conditions and the actions that represent possible actions.

A decision tree is exactly like this apart from it's in a tree form meaning that is a single node at the beginning (being the thing the decision is being made on I think) and then from that initial node there will be n number of nodes that represent each possible decision for each criteria and the criteria should be at the top of the page and at the end of the tree final branches there should be actions and each action should be related to the conditions that were met to reach that branch

Btw sorry if this is a bit confusing
 
B

bradyjoosse

New Member
Joined
Mar 15, 2022
Messages
27
Gender
Male
HSC
2023
cossine said:
So if I am understanding correctly you need create a decision tree i.e. machine learning algorithm. Probably the best way to do this would be to use Python. The main packages you will have to use will be pandas (to read the excel file) and scikit-learn (create a decision tree). You will find many step-by-step guides on medium or towardsdatascience (websites) how to create a decision tree in a few lines of code.

For your IDE(the place you will write code). It is best to use Jupyter Notebook. To do that you will need download and run Anaconda. How to exactly run jupyter notebook will depend on your operating system. It is best to just watch a YouTube video on how to set uo jupyter notebook.

All the best,
cossine
Click to expand...
no I have to make a diagram for my assesment which includes a decision support system using this excel macro.
 
B

bradyjoosse

New Member
Joined
Mar 15, 2022
Messages
27
Gender
Male
HSC
2023
dav53521 said:
A decision tree/table in IPT are a visual representation of what action should occur if certain conditions are met and a decision tree and table are just different ways of representing it.

A decision table is a table that has two main columns one of the being the rules (yes or no to each condition/action) and the conditions that represent certain conditions and the actions that represent possible actions.

A decision tree is exactly like this apart from it's in a tree form meaning that is a single node at the beginning (being the thing the decision is being made on I think) and then from that initial node there will be n number of nodes that represent each possible decision for each criteria and the criteria should be at the top of the page and at the end of the tree final branches there should be actions and each action should be related to the conditions that were met to reach that branch

Btw sorry if this is a bit confusing
Click to expand...
struggling to get it to work for my scenario
 
D

dav53521

Well-Known Member
Joined
Mar 23, 2022
Messages
317
Gender
Male
HSC
2022
bradyjoosse said:
thats what I am not sure about I made my system from scratch but just not sure how to get it into diagram form
Click to expand...
So what are the possible ways the system will provide support to the user and how does the system determine what type of support should be provided to the user?
 
Last edited:
wizzkids

wizzkids

Well-Known Member
Joined
Jul 13, 2016
Messages
332
Gender
Undisclosed
HSC
1998
bradyjoosse said:
Hey,
My visual basic macro code is below which takes inputs from form elements in excel then filters and displays data, I really don't understand what to do as I have looked at a bunch of past examples and videos and still don't understand it.
Click to expand...
I am puzzled by your approach to this assignment. From my interpreting of your VBA code, this is a macro invoked by clicking a FilterButton for sorting a bunch of information about Internet Service Providers, based on three cells containing "UploadSpeed" "DownloadSpeed" and "Budget". It then copies the filtered records to a new spreadsheet. I can't see how this relates to your assignment for a decision support system? Can you explain?
 
You must log in or register to reply here.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top