How can I filter data by row contents? Similar to Excel's filter feature.

[ebloohm]

I would like to view only the rows in which the data in column x is some particular value.

For instance, I'd like to view (and create a group of channels) containing only data from rows where the text in column 2 is MC1.

... guess I can't put a picture inline...

See attached for sample data.

Thanks.

[Brad_Turpin]

Hi ebloohm,

If you'll post or email (brad.turpin@ni.com) an example data set, I'll send you code that does the row filtering.

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[ebloohm]

Thanks Brad.

Will the screenshot of the data from the previous post work?

If not, I can send you a portion of the actual data file.

[Brad_Turpin]

Hi ebloohm,

No, I can't test my script with your screenshot.

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[ebloohm]

OK.  A small sample is attached.  The original file is about 1 1/2 million rows.

[Brad_Turpin]

Hi ebloohm,

This script will break out your loaded data rows by the "Station" channel values.  let me know if you want to filter out the "1;2" suffix or leave it differentiated.  If you want to do the same sort of thing not within a single file but across multiple files, then you'd do better to change your DataPlugin to enable the DataFinder to pull only the desired rows from each file.

OPTION EXPLICIT
Dim i, j, iMax, jMax, RawGroup, Channel, IdChName, OrderChName, Msg, LastTime
Dim IdChDict, IdChannel, OrderChannel, RowDict, RowKeys, RowItems, DataChDict
IdChName = "Station"
OrderChName = "" ' "Time" or ""

Set RawGroup = Data.Root.ChannelGroups(GroupCount)
On Error Resume Next
Set OrderChannel = RawGroup.Channels(OrderChName)
On Error Goto 0

Set IdChDict = CreateObject("Scripting.Dictionary")
Set IdChannel = RawGroup.Channels.Add(IdChName, DataTypeString)
IdChDict.Add UCase(IdChannel.Name), IdChannel
Set Channel = RawGroup.Channels(IdChName) : IdChDict.Add UCase(Channel.Name), Channel

jMax = RawGroup.Channels.Count-1
Set DataChDict = CreateObject("Scripting.Dictionary")
IF IsObject(OrderChannel) THEN
  DataChDict.Add UCase(OrderChannel.Name), OrderChannel
END IF
FOR j = 1 TO jMax
  Set Channel = RawGroup.Channels(j)
  IF NOT IdChDict.Exists(UCase(Channel.Name)) AND UCase(Channel.Name) <> UCase(OrderChName) THEN
    DataChDict.Add UCase(Channel.Name), Channel
  END IF
NEXT ' j

Msg = "Enumerating all the Groups..."
Call MsgBoxDisp(Msg, "MB_NOBUTTON", "MsgTypeNote", 0, 0, 1)
Call CreateIdChannel(IdChDict)
Set RowDict = EnumerateIdRows(IdChannel)

RowKeys = RowDict.Keys
RowItems = RowDict.Items
iMax = UBound(RowKeys)
LastTime = Timer
FOR i = 0 TO iMax
  IF Timer > LastTime+1 OR Timer < LastTime THEN
    Msg = "Sort Extracting Group " & i+1 & " of " & iMax+1
    Call MsgBoxDisp(Msg, "MB_NOBUTTON", "MsgTypeNote", 0, 0, 1)
    LastTime = Timer
  END IF
  Call ExtractIdGroup(RowKeys(i), RowItems(i), IdChDict, DataChDict)
NEXT ' i
Call RawGroup.Channels.Remove(IdChannel.Name)
Call MsgBoxCancel


Sub TextToNumeric(Group, ChanName, ChanType, Factor, Offset)
  Dim RawChannel, NewChannel
  Set RawChannel = Group.Channels(ChanName)
  Set NewChannel = Group.Channels.Add("New|" & ChanName, ChanType)
  L1 = RawChannel.Properties("Number").Value
  L2 = NewChannel.Properties("Number").Value
  ChnLength(L2) = ChnLength(L1)
  Call ChnCalculate("Ch(L2) = CDbl(Ch(L1))")
  IF Factor <> 1 OR Offset <> 0 THEN
    Call ChnLinScale(NewChannel, NewChannel, Factor, Offset)
  END IF
  Call ChnMove(NewChannel, RawGroup.Properties("Index").Value, RawChannel.Properties("GroupIndex").Value)
  Call RawGroup.Channels.Remove(RawChannel.Name)
  NewChannel.Name = Mid(NewChannel.Name, 5)
End Sub ' TextToNumeric()


Sub CreateIdChannel(IdChDict)
  Dim j, jMax, RawGroup, ChListStr, IdChannels, IdChannel
  IdChannels = IdChDict.Items
  Set IdChannel = IdChannels(0)
  Set RawGroup = IdChannel.ChannelGroup
  Call RawGroup.Activate
  ChListStr = ""
  jMax = UBound(IdChannels)
  FOR j = 1 TO jMax
    ChListStr = ChListStr & "Ch(" & IdChannels(j).Properties("Number").Value & ") & ""."" & "
  NEXT ' IdCh
  ChListStr = Left(ChListStr, Len(ChListStr)-9)
  Call ChnCalculate("Ch(" & IdChannel.Properties("Number").Value & ") = " & ChListStr)
End Sub ' CreateIdChannel()


Function EnumerateIdRows(IdChannel)
  Dim j, jMax, RawGroup, RawChannel, RawChnStr, RowDict, StartRow, StopRow, RowMax
  Set RawGroup = IdChannel.ChannelGroup
  Call RawGroup.Activate
  RawChnStr = ""
  jMax = RawGroup.Channels.Count-1
  FOR j = 1 TO jMax
    Set RawChannel = RawGroup.Channels(j)
    RawChnStr = ChnStrAdd(RawChnStr, RawChannel.Properties("Number").Value)
  NEXT ' j
  Call ChnMultipleSort(IdChannel, RawChnStr, 0, 1)
  L3 = IdChannel.Properties("Number").Value
  Set RowDict = CreateObject("Scripting.Dictionary")
  StartRow = 1
  StopRow = 1
  RowMax = ChnLength(L3)
  Do While StopRow <= RowMax
    T1 = ChT(StartRow, L3)
    StopRow = ChnFind("UCase(Ch(L3)) <> UCase(T1)", StartRow+1)
    IF StopRow = 0 THEN StopRow = CLng(RowMax+1)
    RowDict.Add T1, StartRow & "|" & CLng(StopRow-StartRow)
    StartRow = CLng(StopRow)
  Loop ' Until End Of Channel L3
Set EnumerateIdRows = RowDict
End Function ' EnumerateIdRows()


Function ExtractIdGroup(RowKey, RowItem, IdChDict, DataChDict)
  Dim j, jMax, RawGroup, RawChannel, NewGroup, NewChannel, RawChnStr, NewChnStr, DataChannels, Cols
  Dim StartRow, RowCount, NewDataType, NewDataDisp, NewOrderCh, IdChannels, IdChannel, OrderChannel
  Cols = Split("|" & RowItem, "|")
  IF UBound(Cols) < 2 THEN Exit Function
  StartRow = CLng(Cols(1))
  RowCount = CLng(Cols(2))
  IF Data.Root.ChannelGroups.Exists(RowKey) THEN Call Data.Root.ChannelGroups.Remove(RowKey)
  Set NewGroup = Data.Root.ChannelGroups.Add(RowKey)
  Call NewGroup.Activate
  IdChannels = IdChDict.Items
  jMax = UBound(IdChannels)
  FOR j = 1 TO jMax
    Set IdChannel = IdChannels(j)
    NewGroup.Properties.Add IdChannel.Name, ChV(StartRow, IdChannel.Properties("Number").Value)
  NEXT ' j
  RawChnStr = ""
  NewChnStr = ""
  DataChannels = DataChDict.Items
  jMax = UBound(DataChannels)
  FOR j = 0 TO jMax
    Set RawChannel = DataChannels(j)
    Set NewChannel = NewGroup.Channels.Add(RawChannel.Name, RawChannel.DataType)
    ChnLength(NewChannel) = RowCount
    RawChnStr = ChnStrAdd(RawChnStr, RawChannel.Properties("Number").Value)
    NewChnStr = ChnStrAdd(NewChnStr, NewChannel.Properties("Number").Value)
  NEXT ' j
  Call DataBlCopy(RawChnStr, StartRow, RowCount, NewChnStr, 1)
  NewChannel = ""
  On Error Resume Next
  Set NewChannel = NewGroup.Channels("Value")
  On Error Goto 0
  IF OrderChName <> "" THEN
    NewChnStr = ""
    jMax = UBound(DataChannels)
    Set OrderChannel = NewGroup.Channels(DataChannels(0).Name)
    FOR j = 1 TO jMax
      Set NewChannel = NewGroup.Channels(DataChannels(j).Name)
      NewChnStr = ChnStrAdd(NewChnStr, NewChannel.Properties("Number").Value)
    NEXT ' j
    Call ChnMultipleSort(OrderChannel, NewChnStr, 0, 1)
  END IF ' can order the rows of each extracted Group
Set ExtractIdGroup = NewGroup
End Function ' ExtractIdGroup()

Brad Turpin

DIAdem Product Support Engineer

National Instruments

[ebloohm]

Thank you.

[AndiMEC]

Hello,

even if this topic is already a while ago this pretty looks atfter what i need.

But i have a few issues about the script from Brad_Trupin.

-Where in the script and how can i pretend according to which row (value) is filtered (so not for all row(values) will be filtered and created a group)

or even better after more pretende row values will be filtered (like in excel)

-How can i manage it that the filtered channels are not sorted (or sorted by time-channel/chronologically)

Thanks a lot

[hnesmith]

hello AndiMEC,

i'm not sure i understand what you're asking. say you have a column of numeric data, and you want to filter the data to show only numbers < 10. would like you like to filter it further or for it to maintain the original row placement? please clarify what you mean. 

[AndiMEC]

Hello,

i juat want to get the same as in the script of Brad_Trupin is done only a bit modifyed.

Example i have 5 text channels and i want to filter them (like in exel), one channel includes the values M1, M2, M3 ,M4, and i want to filter after e.g  M1 AND M3 (like in excel)

And how do i get it that the result channels aren't sorted (like in the original channel)