' Name: Table.search4value_occurence ' ' Title: Finds values in a field and reports starting & ending date in a text file. Useful to find occurences ' of (NO)DATA - values in time series of daily measurements. ' ' Topics: Tables ' ' Description: Finds occurences of data values, e.g. NODATA (-9999) in the active field of the active Table, ' reports starting & ending date (if the table has information stored in fields "year","month","day"), ' writes a report to a text file. ' ' Requires: A table must be the active document, a field must be active(selected). The table needs date information ' stored in fields "year", "month", "day". ' ' Author: Daniel Schwandt, December 2000 ' ' Self: ' ' Returns: ' theTable = av.GetActiveDoc dat = theTable.getname.asstring theVTab = theTable.GetVTab theSet = theVTab.GetSelection theField = theTable.GetActiveField if (theField = nil) then MsgBox.Info("Please select the data field in the table", "No Field selected") return nil else 'continue end ' snippet from a script VTab.ReturnUnique if (theField <> NIL) then bSelectionOnly = false bCaseSensitive = false bSortAscending = true t = ListBox.Make t.DefineUniqueFromVTab(theVTab, theField, bSelectionOnly, bCaseSensitive, bSortAscending) ' ' Before using t we must add it to some dialog. ' d = Dialog.Make(false, false, false, false, false) d.GetControlPanel.Add(t, Rect.Make(0@0, 100@100)) ' ' Select all rows. ' t.SetSelectionStyle(#LISTBOX_SELECTION_MULTIRANGE) rctAll = rect.Make(0@0, 1@t.GetRowCount) bAppendSelection = false t.SetSelection(rctAll, bAppendSelection) lValues = t.GetSelection.Clone else lValues = {} end 'end snippet 'definition of data(NODATA)-values fehl = MsgBox.ChoiceAsString(lValues,"Select (no)data value you search for:","search for value occurence") 'fehlin = MsgBox.Input("Data(NODATA) value you search for:", "search for value occurence", "-9999") 'fehl = fehlin.asNumber 'fehl = -9999 theQuery = "["+theField.getname+"] = " + fehl.asstring 'query and apply selection theVTab.Query(theQuery, theSet, #VTAB_SELTYPE_NEW) theVTab.UpdateSelection datulist = {} year = theVTab.findfield("year") month = theVTab.findfield("month") day = theVTab.findfield("day") for each record in theSet v = theVTab.ReturnValue(theField, record) if (v = fehl.asnumber) then dday = theVTab.ReturnValue(day, record) dmonth = theVTab.ReturnValue(month, record) dyear = theVTab.ReturnValue(year, record) 'the format of the date is set with the .SetFormat() request datum = Date.Make(dday.asString+"."+dmonth.asString+"."+dyear.asString, "d.M.yyyy").SetFormat("MMMM d, yyyy") datulist = datulist.Add(datum) else 'do nothing end end theVTab.GetSelection.ClearAll theVTab.UpdateSelection 'MsgBox.Listasstring(datulist,"datulist", "date list ...") if ((datulist.isempty).not) then 'create text file datnum = dat.count filetxt = dat.Left(datnum-4)+".txt" txtfile = FileDialog.Put( filetxt.asfilename, "*.txt", "output (no)data occurence file") if (txtfile = nil) then return nil end txtfile = LineFile.Make( filetxt.AsFileName, #FILE_PERM_WRITE ) txtfile.WriteElt ("occurence(s) of "+fehl.asstring.quote+" in field "+theField.getname.quote+" in table(file) "+dat.asstring.quote+" :") msg = "" for each d in 0 ..(datulist.count - 1) datu = datulist.get(d) if (d = 0) then datvor = nil else datvor = datulist.get(d-1) end if (d = (datulist.count -1)) then datnach = nil else datnach = datulist.get(d+1) end if ((datvor.= (datu - 1.asdays)) and (datnach.= (datu + 1.asdays))) then 'middle elseif ((datvor.= (datu - 1.asdays)) and ((datnach.= (datu + 1.asdays)).not)) then 'end txtfile.WriteElt ("to "+datu.asString) msg = msg+" to "+datu.asString elseif (((datvor.= (datu - 1.asdays)).not) and (datnach.= (datu + 1.asdays))) then 'start txtfile.WriteElt ("from "+datu.asString) msg = msg+NL+"from "+datu.asString else 'single txtfile.WriteElt ("single occurence "+datu.asString) msg = msg+NL+"single occurence "+datu.asString end end MsgBox.Report(msg,"occurence(s) of "+fehl.asstring.quote+" in field "+theField.getname.quote+" in table(file) "+dat.asstring.quote+" :" ) else MsgBox.Info("No occurence of "+fehl.asstring.quote+" in field "+theField.getname.quote+" in table(file) "+dat.asstring.quote , "No occurence !") end