Cutting one dataset into many with certain criteria

[Kari Koivisto]

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

[Kari Koivisto]

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

[Bernhard Kantz]

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

[Author]

Posts