Cutting one dataset into many with certain criteria

SupportCommunity General Cutting one dataset into many with certain criteria

Ansicht von 3 Beiträgen - 1 bis 3 (von insgesamt 3)
  • Autor
    Beiträge
  • #12653
    Kari KoivistoKari Koivisto
    Mitglied

    I have one big dataset, only one column but thousands of rows.

    How do I (with macro?) make a criteria something like this:

    1. check the value of the row 1, if value is > -0.5 then copy the value to the new dataset.

    2. After this check the value of the next row with the same criteria. And also copy the value to the next row of the same dataset.

    3. Do this as long as the value stays within the criteria.

    4. If value is -0.5 then create a new dataset as mentioned in point 1.

    Thanks in advance…

    Kari

    #8316
    Kari KoivistoKari Koivisto
    Mitglied

    I have one big dataset, only one column but thousands of rows.

    How do I (with macro?) make a criteria something like this:

    1. check the value of the row 1, if value is > -0.5 then copy the value to the new dataset.

    2. After this check the value of the next row with the same criteria. And also copy the value to the next row of the same dataset.

    3. Do this as long as the value stays within the criteria.

    4. If value is < -0.5 (for example) then cut (delete) and close the dataset. After this check the next row, do this as long as the value is again > -0.5 then create a new dataset as mentioned in point 1.

    Thanks in advance…

    Kari

    #9059
    Bernhard KantzBernhard Kantz
    Moderator

    You need to write a macro to be able to create new datasets. Here are the basic steps:

    To access the value of an existing dataset in the root folder of the current database you can use the following code:

    
    Sub AccessValue()
        Dim oDataSet As DataSet
        Dim vtValue As Variant
        
        Set oDataSet = ThisDatabase.RootFolder.Object("Dataset")
        vtValue = oDataSet.Value(fpDataComponentY)
    End Sub
    

    To create a new dataset you can use the following code (you can also start macro recording and create a new dataset manually to get some similar code generated):

    
    Sub CreateNewDataset()
        Dim oDataSet As DataSet
        
        Set oDataSet = ThisDatabase.RootFolder.Add("NewDataset", fpObjectTypeDataSet)
        ' omit the next line if you need an empty dataset
        oDataSet.Value = Array(1, 2, 3, 4) ' some values
    End Sub
    

    Appending a value to a dataset basically looks like that:

    
    Sub AppendValueToDataset()
        Dim oDataSet As DataSet
        
        Set oDataSet = ThisDatabase.RootFolder.Object("Dataset")
        
        With oDataSet
            .NumberOfRows = .NumberOfRows + 1
            .Value(, , .NumberOfRows) = 12345 ' the new value
        End With
    End Sub
    

    The rest is just a matter of running over the retrieved value (using a for loop for instance), append the required values to the dataset you created before and creating new datasets as required.

    Some related tips: Take a look at the topic Working with Data Sets in the online help for a complete discussion of FlexPro data access in VBA. You could use FPScript functions to detect the value ranges for the new datasets more effectively (see for instance ValuesAboveLevel or LevelCrossings). It is also possible to use the FPScript functions from VBA (see the sample for the FPScriptFunction property).

    Support
    support@weisang.com

Ansicht von 3 Beiträgen - 1 bis 3 (von insgesamt 3)
  • Du musst angemeldet sein, um auf dieses Thema antworten zu können.