VS 2010 [RESOLVED] Partition and Rank datatable by a certain column-VBForums
Results 1 to 6 of 6

Thread: [RESOLVED] Partition and Rank datatable by a certain column

  1. #1

    Thread Starter
    Member
    Join Date
    Aug 2014
    Posts
    33

    Resolved [RESOLVED] Partition and Rank datatable by a certain column

    Hi to all,
    I must partition by Type and rank the following datable ( called dt) by LoanAmt column with ties

    Type LoanAmt Rnk
    ====================
    A 14
    A 15
    A 13
    A 11
    A 11
    B 17
    B 18
    B 17
    B 16
    (see attachment)
    As a new learner, I have read about Linq to datasets and something in the lines of

    Dim Rnks As DataTable = dt
    Dim query = From Rnk In dt.AsEnumerable() Order By Rnk.Field(Of double)("LoanAmt") Descending Select Rnk

    With the above query, how do if fill up the Rnk column above? Any snippet?
    Thanks
    Attached Images Attached Images  
    Last edited by gbhs; Aug 22nd, 2015 at 07:32 PM.

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    21,492

    Re: Partition and Rank datatable by a certain column

    So you want to sort by Type alphabetically ascending, then you want to rank each entry by LoanAmt. In your attached image example, would that be 1 to 10 ranking or 1 to 5 (for A Types) and 1 to 5 (for B Types)?

  3. #3

    Thread Starter
    Member
    Join Date
    Aug 2014
    Posts
    33

    Re: Partition and Rank datatable by a certain column

    Hi Paul
    Its a 1 to 5 ranking for type A and similar for Type B and the Ranks will be in the Rnk column.
    How is it done?

    A function like this one in the link below but for different Types

    http://code-programmming.blogspot.co...c-example.html
    Last edited by gbhs; Aug 23rd, 2015 at 04:17 AM.

  4. #4
    Frenzied Member IanRyder's Avatar
    Join Date
    Jan 2013
    Location
    Healing, UK
    Posts
    1,230

    Re: Partition and Rank datatable by a certain column

    Hi,

    From what I understand of your question, I would use a combination of LINQ and traditional For Loops here to make things easier to read and understand.

    In your case you need to Group your DataRows by the Type column, Order the LoanAmt Column in Descending order and then Rank each record within the Loan Group accordingly. Have a Play with this:-

    vb.net Code:
    1. Private myData As New DataTable
    2.  
    3. Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    4.   'Start by Grouping the Different Loan Types
    5.   Dim loanTypes As IEnumerable(Of IGrouping(Of String, DataRow)) = myData.Rows.Cast(Of DataRow).GroupBy(Function(x) x(0).ToString)
    6.   Dim rankValue As Integer
    7.  
    8.   'Iterate through Each Loan Type and Reset the Rank Value at the Start of Each Type
    9.   For Each currentType As IGrouping(Of String, DataRow) In loanTypes
    10.     rankValue = 0
    11.     'Loop through Each of the Loan Records in the Order Required, Increment the Rank Value and Set the Rnk Value
    12.     For Each loanRecord As DataRow In currentType.OrderByDescending(Function(x) CDec(x(1)))
    13.       rankValue += 1
    14.       loanRecord(2) = rankValue
    15.     Next
    16.   Next
    17.  
    18.   'Re-Order the DataTable in Type and Rank Order
    19.   myData.DefaultView.Sort = ("Type ASC, Rnk ASC")
    20. End Sub

    All you need to do now is add the DataTable to a suitable control to display the results.

    Hope that helps.

    Cheers,

    Ian
    Last edited by IanRyder; Aug 23rd, 2015 at 06:43 AM. Reason: Noticed Numeric Type Mistake

  5. #5
    New Member
    Join Date
    Nov 2017
    Posts
    8

    Re: Partition and Rank datatable by a certain column

    Quote Originally Posted by IanRyder View Post
    Hi,

    From what I understand of your question, I would use a combination of LINQ and traditional For Loops here to make things easier to read and understand.

    In your case you need to Group your DataRows by the Type column, Order the LoanAmt Column in Descending order and then Rank each record within the Loan Group accordingly. Have a Play with this:-

    vb.net Code:
    1. Private myData As New DataTable
    2.  
    3. Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    4.   'Start by Grouping the Different Loan Types
    5.   Dim loanTypes As IEnumerable(Of IGrouping(Of String, DataRow)) = myData.Rows.Cast(Of DataRow).GroupBy(Function(x) x(0).ToString)
    6.   Dim rankValue As Integer
    7.  
    8.   'Iterate through Each Loan Type and Reset the Rank Value at the Start of Each Type
    9.   For Each currentType As IGrouping(Of String, DataRow) In loanTypes
    10.     rankValue = 0
    11.     'Loop through Each of the Loan Records in the Order Required, Increment the Rank Value and Set the Rnk Value
    12.     For Each loanRecord As DataRow In currentType.OrderByDescending(Function(x) CDec(x(1)))
    13.       rankValue += 1
    14.       loanRecord(2) = rankValue
    15.     Next
    16.   Next
    17.  
    18.   'Re-Order the DataTable in Type and Rank Order
    19.   myData.DefaultView.Sort = ("Type ASC, Rnk ASC")
    20. End Sub

    All you need to do now is add the DataTable to a suitable control to display the results.

    Hope that helps.

    Cheers,

    Ian
    Sir am a beginner
    how can I add the DataTable to a suitable control to display the results. Please.

  6. #6
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    21,492

    Re: [RESOLVED] Partition and Rank datatable by a certain column

    Use a DataGridView. To bind it...

    DataGridView1.DataSource = myData.DefaultView

    In future, please start your own thread. When you hijack a resolved thread, it is revived and everyone who originally answered is notified. It's not good forum etiquette...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.