- 20 Minutes to read
- Print
- DarkLight
- PDF
Download2
- 20 Minutes to read
- Print
- DarkLight
- PDF
Coming Soon
Store nulls for continuous field on load - and make available to audit.
Custom named script from full field name in audit and solution explorer
Version 4.12.12.1
Added Expression Functions spacex() and trimx() and single character implementation of replace()
Toggle JSON view on and off in Supplementary Script window/tab.
Version 4.12.11.1
Server side shadow folder for scripts and snippets
New Script Method STOPIF(condition-true)
{
"method": "STOPIF",
"condition": "count([CheckData.DS-Join1],1) != 104981",
"description": "expected join records",
"project": "MyProject"
}
Can now join (if resources available) mixed fixed and variable width continuous strings.
Explorer UI for Manage & Open Projects now shows project creation date.
Push Script has session persistent overwrite check-box
Script editor shows if working with remote or local script
Create project check for OS dependant invalid characters in name.
Version 4.12.9.1
Can select from Analyze Field Form.
Updated Panel Functions
- min
- max
- avg
- median
- sum
- unique
- count
- nulls
- pop%
- name
- nameex
- description
- avgwidth
- maxwidth
- fieldtype
- datatype
- datasize
Drag Field from Solution Explorer onto Data Audit: Selects field if in view.
Drag Table from Solution Explorer onto Data Audit: Filters view to that table.
String Field Length and allowOverflow
Panels have new function support
- name
- description
- avgwidth
- maxwidth
And can handle functions for String Fields where the data is available
Script Folded Section description now displayed in detail column and Folded Script sections are listed in Goto menu.
Added Context Menu to Data Audit
Core max string field width now variable. default increased from 1024 to 4096.
Major New Field Type/Implementation - Variable Continuous String.
STRLEN(A) function and expression now count in characters not bytes.
WORDLEN(A) new function and expression, counts number of words in string.
PATTERN(A),PATTERNUC(A) ,UPATTERNUC(A) new function and expression, replace a-z/A-Z/0-9 with aA9.
Also, in Expressions.
- pattern()
- patternuc()
- upatternuc()
- kaskey()
- extractkey
Major New Feature: Field Analyzer
- CharDiscrete
- CharRecords
- CharOccurrences
- CharSummary
- SLenDiscrete
- SLenRecords
- WordsDiscrete
- WordsRecords
New columns added to Audit
- Description
- Max Width
- Avg Width
Default for ordinalizing string and numeric functions and expressions is now integer sized.
Optional key in ExportIntoTable API (and UI) to force attempt to load all fields as discrete.
Added Recent Projects Menu item.
Version 4.11.11.2
Can now extended-select and copy from folded code.
Model Programmer share and un-share buttons.
Version 4.11.11.1
Core now using Windows C++ compiler v 143. (2 year update)
Modeling
- System models now directly available if shared.
- Right click on System model list to share or un-share.
- Shared models shown in green
- Hover over models in System, User or Profile forms for Tooltip description.
Script Editor: Removed use of clipboard where possible.
Standard Grid/Graph control Error box added
First and Last discrete available as operators in field to dataset drop dialog.
Improvements to DataSource connectors.
Search Functions in Engineering
Version 4.10.26.1
Improvement to data source connectors.
Added bulk load for data sources.
Version 4.10.26.1
Added sslMode setting to MySQL connectors.
Added full connection string MySQL source with connection test.
Advanced error reporting on MySQL connections
Version 4.10.24.1
Support for MySQL 8
New layout for correlation window.
Fixed bug exporting into table returning failed - when it worked (when all continuous fields)
Removed "compromised" Azure nuget.
Fixed (DRM-44) Management Console - Failed adding user but previous value added anyway
Version 4.10.14.1
Report links or images can now be sent to email contacts.
Multi-Field Statistics report can now be previewed in browser.
Fixed bugs in standardized report:
- dependent filters did not reset correctly.
- filters labels do not display underscore character
Statistics Panel is dashboard (design mode) can be set to box-whisker, grid or both using micro-control buttons.
API calls ExecuteReport and ExecuteCustomReport now have an optional "records" key.
{
"method": "ExecuteCustomReport",
"report": "Data Standardized Report",
"records": 8192,
"byReference": true,
"filters": [],
"project": "MyProject"
}
Datetime functions ADDMINUTES(A,B) and ADDHOURS(A,B)
Statistical function
- REMOUT(A) produces a new field with outliers removed ( keep values (q1-1.5*iqr) <= x <= (q3+1.5*iqr) else set null)
- REMOUTSDV(A,B) remove outliers more than B standard deviations below or about the mean of field A
- REMOUTBTWN(A,B,C) removes values from field A that are not (inclusively) between B and C
Accuracy of ordinalized doubles can reach 6 decimal places.
Rationalized Function Menus
Version 4.10.11.1
Statistical functions added to Engineering
| |||||||||||||
Main Function & Function context menu has more detailed navigation to specific functions
Drop field "As Statistics" onto Dashboard.
Profile and Index Profile now have the ability to invoke a modelling routine.
Added String Functions :
UPPER(A) |
LOWER(A) |
REMOVENUM(A) |
REMOVENONNUM(A) |
REMOVENONNUMEX(A) |
MID(A,B) |
TRIM(A) |
Version 4.10.3.1
Removed Maths module from explorer (now processed server side)
Appropriate profiles will return a "covariance" JSON entry within the "model" section, with the following information
A regression line in the Y-X plane is defined by Y=(lgrad*X) + lconst. lgrad
and lconst
are given as properties of covariance
.
The X
and Y
properties of covariance
give the min/max X and Y values contained in the scatter plane, which can help when plotting the regression line boundaries.
{
"correlation": 0.4556458656801002,
"lgrad": 1.2237627961494968,
"lconst": -63.15815174267849,
"covariance_matrix": [
[
0.00041898174921705365,
0.0005127342769574688
],
[
0.0005127342769574688,
0.00302228022457938
]
],
"eigenvectors": [
[
-0.9824503352874865,
0.18652436487897542
],
[
0.18652436487897542,
0.9824503352874865
]
],
"eigenvalues": [
0.0003216359273204313,
0.0031196260464760018
],
"contribution": [
9.346452835312608,
90.6535471646874
],
"X": [
51.454752,
51.549369
],
"Y": [
-0.236769,
-0.002275
]
}
Listener has search and filter for JSON results
Click on the key icon for first and second level object selection.
Version 4.10.1.1
Changed ColumnMath module
---------------------------------------------------------------------------------------------
Do Not Ordinalize option available for CONCAT() function.
Chart option for regression line on scatter plot
Max rows in Multi-Dimensional profile set to 5,000,000 [MAX_YAXIS_DISPLAYED_ROWS]
Self-Joins allowed.
DCAST(A) and WILDDCAST(A) now available for both discrete and continuous fields
Candlestick will ignore flip
Scatter plot Y axis now correctly sets min and max value.
Geo Functions NEAREST(LT,LN,TLT,TLN) and DIST2NEAREST(LT,LN,TLT,TLN) can now have an optional "limiter" key in their definition. This key is either the short name of a distance field already on the target table, or #immediate value (in meters).
DataSet Sample Function
DataSet Bottom Function
Function: DiscreteIntegerSequence. For tables with less than just under 1 million records
Set "doubleDigits":10 in config file to load doubles to 10 decimal places.
CreateTableFromJSON will now load native long and double data.
Added fileIsArray flag to CreateTableFromJSON. set true when file to load in a JSON array rather than NDJSON.
CTOD(A) now works with Integer and Long Integer data,
Multi Dimensional geometric distance functions: "GMNEAREST()", "DIS2GMNEAREST()". These support up to 16 dimensions. They can be used for such things as applying a cluster (calculated by KMeans on a sample) to a whole table
New API - CreateTableFromModel. create and populate in table from model data embedded in the call itself.
Join dialog correctly displays underscores in field names
Added right-click Create DataSet Collection in Project Explorer.
PScript module can execute python or rscript - set "language" key in "param" : {...} to "python" (default) or "r"
{
"method": "XProcess",
"name": "PScript",
"wait": true,
"param": {
"language": "python",
"script": "%PLUGINS%kmeans.py",
"inFile": "%DATAPATH%XP_YP.txt",
"outFile": "%DATAPATH%XP_YP_clusters.txt",
"params": [
"5"
]
},
"project": "MyProject"
}
Plugin setup UI table combo now sorted.
PScript executable will adjust to Python location in config (used for python models such as K-means)
MultiFieldStats: can now be created with an alternative "measure" and "categoryField" keys, cf. "entries":[]
First Examples for Geo Functions: "DISTANCE(A,B,C,D)", "NEAREST(LT,LN,T.LT,T.LN)", "DIST2NEAREST(LT,LN,T.LT,T.LN)"
Fixed bug in IFS(), where first value returning true for nulls.
Can now quantile discrete fields on tables with more than 2 billion records
API: ModelSnippetUpdate
{
"method" : "ModelSnippetUpdate",
"name" : "Simple KMeans",
"targetName" : "KMeans Param",
"language" : "python",
"project": "MyProject"
}
Partial prototype of Workbooks As Projects.
Fix: Clear script editor search panel on pull script.
Dashboard: Added Static Panels
Fix: Dashboard: No longer clears applied filter when toggling in and out of design mode
---------------------------------------------------------------------------------------------
Multi Stats -> Automate button in micro-toolbar
Query Matrix Report
Minor Fixes
---------------------------------------------------------------------------------------------
Note on 'Stuck' Drag-Drop short cuts in Project Explorer window
- This behaviour is Framework related; to remedy simply press the Escape key.
Fixed:
- One character header names have an underscore appended after calculating delimited headers
- Trim leading and trailing spaces in guessed field headers.
Improved Processed Column's Screen, Removed old UI and tidied Multi Function Profile
Delimited File Window, types only set on Right-Click; added a dismissible reminder.
Bulk Delimited File creation - with Auto Guess definition.
Shift-Click to select a block of files in the Remote Directory Dialog
---------------------------------------------------------------------------------------------
Processed/Calculated Columns helper
In a Multi Function Profile, to work with processed columns as a whole set; click on the 'eye' icon im
This will open a new UI to Add/Delete and modify all processed columns
Create entries for multiple CreateTableFromFile
Model & Column Processing order can be changed in Multi-Function Profile options
In the example below:-
- ID, Sum XP and Sum YP are original fields
- Cluster, Centre-X and Centre-Y and produced by the KMeans Model
- X-Diff, Y-Diff and rootDiff are then calculated
---------------------------------------------------------------------------------------------
Fixes due to upgrade.
More dimensions available on small tables for multi-d profile count.
---------------------------------------------------------------------------------------------
Upgraded: Telerik Control Pack. Visual Studio: C++ Compiler.
Custom Decode default value
Improved some LMATH & DMATH performance
Added Datatime function ADDSECONDS(A,B)
---------------------------------------------------------------------------------------------
Dashboard - AND/OR combo when multiple-select dataset combo is in use.
Dashboard - Design mode Right-click menu on dataset combo to clear.
Dashboard - Warning when about to save Dashboard with dataset applied.
Dashboard - Calculation errors appear in info panel (auto-opens on new error)
---------------------------------------------------------------------------------------------
Datetime function ADDSECONDS(A,B)
Annotations added to GenProfile & Data Model
Added 'R' language version for discrete programmed fields and model processor
Delete Last Row in Dashboard
---------------------------------------------------------------------------------------------
Drag fields or dataset collections directly into dashboard slots
Move dashboard slots with ctrl-drag & drop [not all controls]
Programmed models - simple charts in detail area
slack now posts images to DataJet's servers.
Drag flag fields directly into waterfall and dataset collection with no rename. (unless duplicate)
---------------------------------------------------------------------------------------------
limit can be set in platform download. "limit":1024. default 1024.
Duplicates with cause an error, different count will cause an error.
Seems OK with limit of 128 or above. Set limit to 50 to test error reporting.
Download JSON: Will nor allow dupes. Seems to download the correct number of records.
---------------------------------------------------------------------------------------------
Set Parameters for Model Processing
CreateTableFromJSON strips \r \n and replaces \t with " "
Rotatable Connected Report
---------------------------------------------------------------------------------------------
Fix for small table LMATH oridnalizers
Extended ETL Summary table method ETLSummaryFromTableEx
Experimental DataModel Python Functions
Possible Fix for forcedNumeric: requires Suite 4.6.23.1 installed first
Experimental Profile post processing on DataModel with Python Snippet
Server: Write to UTF8 without BOM
---------------------------------------------------------------------------------------------
(Beta) Embedded Text in Dashboard. From Menu->Engineering (see inline help)
Updated Mongo Nugget (fixes bug in script editor true/false)
Flag fields :-
- Have own icon is tree
- Added to type filter on search
- Smarter drag and drop
- Shift/Ctrl drop for inverted flag
Big Performance improvement for discrete-discrete joins
Fix: removed totals from multi measure when post-function(s) applied
Added further help expanders and out-calling "?" to misc windows.
Access floating help from the Tools menu.
---------------------------------------------------------------------------------------------
New, Faster routines to ordinalize double fields.
Pre-prep a field with unique values from another field for faster load - designed for large discrete string fields.
---------------------------------------------------------------------------------------------
Fix to MergeTablesOnJoin
---------------------------------------------------------------------------------------------
Some work toward bringing workbooks up to date Example :-
---------------------------------------------------------------------------------------------
IF ELSE ENDIF on Script Menu
Set Palette on Script Menu
Explorer implements Palette Correctly
Script Methods are inserted after current selected location in script (previously always appended at end)
DataView allowed in Standardized Reports
DataView allowed on Right Side of Connected Reports
---------------------------------------------------------------------------------------------
IF condition added: XXX == YYY and XXX != YYY
---------------------------------------------------------------------------------------------
Fix: MergeTablesOnJoin
Experimental implementation of FloatingHelpWindow
---------------------------------------------------------------------------------------------
Fix: Serialization of DataView should not contain records.
---------------------------------------------------------------------------------------------
MergeTablesOnJoin
Fix: Edit title of DataView in Dashboard
D/M/Y & M/D/Y when Y is only 2 digits (assumes 2000)
8k cell size max in View (with truncation)
Non case sensitive check for existing project in CreateProject
---------------------------------------------------------------------------------------------
Centralized server update for inline help, external help links and prefabs
custom-prefabs for local (client specific) pre-defined reports.
Icons in DataSource management
More choices in Standardized Report Layout.
---------------------------------------------------------------------------------------------
Fixed url link format in PostMan
Count Discrete field - for simple crosstabs
Fix for optional "continuous" JSON key not present in CreateTableFromJSON
---------------------------------------------------------------------------------------------
Inline help and links auto-update from central server
Small crosstab "Feature" chart
Standardized Report copy/post image
Report - "Drop Here" hints
---------------------------------------------------------------------------------------------
Defines: keys within keys within keys.
---------------------------------------------------------------------------------------------
CreateAuditTableFromTable
ETLSummaryFromTable
Change to inline help highlighter
Script Editor JSON right-click "Description" for all objects
Change to From Set Template Workflow (Dialogue) - and target Sheet selection.
---------------------------------------------------------------------------------------------
Fix to script IF:count
Supplementary viewer: show and copy from folds.
---------------------------------------------------------------------------------------------
Script IF-THEN-ELSE changes
strlen(<integer field>) allowed in expressions
---------------------------------------------------------------------------------------------
Fix: Merge Tables
Change to grid column width management for performance.
---------------------------------------------------------------------------------------------
Extended functionality in CreateTableFromJSON. (see script editor method help)
Various Profile reports, initial grid column width adjustment.
Various Profile reports from tree context menu; may display in split mode with pie or bar.
Fixed lock code bug in script (harmless).
Explorer with remember hd/low res start up state.
---------------------------------------------------------------------------------------------
JSON support
---------------------------------------------------------------------------------------------
Full rebuild after Visual Studio Update
Some updates to DataView grid in dashboard.
---------------------------------------------------------------------------------------------
Stable Folded Script when running
Proto Data view in Dashboard/Standardized Reports
------------------------------------------------------------------------------------------- Fixed to folding script. Show state, time and click for errors. stops on error.
Fix folding order bug
Experimental Script Fold/Unfold - Now should Run (backup unfolder versions for now)
---------------------------------------------------------------------------------------------
"Custom Data" support.
---------------------------------------------------------------------------------------------
Preserve spaces in excel template export
---------------------------------------------------------------------------------------------
Loader does not automatically remove training spaces, trimTrailingSpaces boolean key added to loaders (default false)
Channel management API
Added post to Channel context menu for simple profiles
---------------------------------------------------------------------------------------------
ExportIntoWorkbook with template explicitly setting cell references
Right click on report in tree to post to channel. (slack etc)
---------------------------------------------------------------------------------------------
Create Slack channels with CreateMessagingHook
Fix: ExecuteReport with empty panels
Suffix options with MergeTables
"Stop" method added to Script Editor (and Tools Menu)
---------------------------------------------------------------------------------------------
See ExecuteReport context menu for new keys.
---------------------------------------------------------------------------------------------
ExecuteReport optional "exportAsTable" : true. If using iterator the reports will be written as workbook tables,
Attempt to improve Tab drag responsiveness.
Increased dimension size of count distinct profiles
---------------------------------------------------------------------------------------------
Iterator in ExecuteReport
Added Total row to count distinct profiles and some multi function profiles
Dashboard Selection Mode in micro toolbar,
---------------------------------------------------------------------------------------------
Overflowing continuous string will cause a load error unless "allowOverflow": true in CreateTableFromFile, LoadTableFromFile and UpdateTableFromFile
Custom Decode - options to show headers when selecting code and decode column from ComboBox
Experimental, specific external API support
{
"method": "PassThroughAPI"
"Secret": "EON-SANDBOX",
"url":"https://sandbox.app.eongroup.co",
"command":"EonListProducts"
}
{
"method": "PassThroughAPI"
"Secret": "EON-SANDBOX",
"url":"https://sandbox.app.eongroup.co",
"command":"EonRetrieveProduct",
"id":"1648"
}
{
"method": "PassThroughAPI"
"Secret": "EON-SANDBOX",
"url":"https://sandbox.app.eongroup.co",
"command":"EonUpdateProduct",
"id":"1648",
"package" : {
"description":"new description"
}
}
CopySourceTable & CopySourceJoin from other projects
[
{
"method": "CopySourceTable",
"sourceProject": "MyOtherProject",
"table": "Customer",
"allFields": true,
"project": "MyProject"
},
{
"method": "CopySourceTable",
"sourceProject": "MyOtherProject",
"table": "Sales",
"allFields": true,
"project": "MyProject"
},
{
"method": "CopySourceJoin",
"sourceProject": "MyOtherProject",
"primaryTable": "Customer",
"foreignTable": "Sales",
"project": "MyProject"
}
]
ReadProjectKey and WriteProject key for script control. Project2 script will fail if key does not exists.
[
{
"method": "WriteProjectKey",
"key": "LOADCOMPLETE",
"project": "Project1"
},
{
"method": "ReadProjectKey",
"key": "LOADCOMPLETE",
"sourceProject": "Project1",
"project": "Project2"
}
]
---------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------
High performance MergeTables method [with inline help]
Decode edit will show any new codes that appear in data as decoded to null in grid
---------------------------------------------------------------------------------------------
Fixed Script Editor - double paste bug.
Rolled up fixes, latest help.
Started to add Methods in Script Editor. [reading from core]
---------------------------------------------------------------------------------------------
Change to MapFieldNamesFromTable
Change to MapMissingFieldsFromTable
---------------------------------------------------------------------------------------------
Optional "makeValidFieldNames" flag (default false) added to MapFieldNamesFromTable and MapMissingFieldsFromTable
Audit will not fail in there are invalid datasets (reported as count -1)
Ctrl-H in Script JSON window will open help panel automatically
Ctrl-L will open list of current variables in script from any JSON window
---------------------------------------------------------------------------------------------
MapMissingFieldsFromTable
{
"method": "MapMissingFieldsFromTable",
"sourceTable": "MissingMappingTable",
"sourceField": "newField",
"typeField": "Type",
"targetTable": "ModifyingTable",
"project": "MyProject"
}
MissingMappingTable looks like..
NewField | Type |
Created Row | STRING |
Created Row ID | STRING |
MapFieldNamesFromTable
{
"method": "MapFieldNamesFromTable",
"sourceTable": "RenameMappingTable",
"sourceField": "FromField",
"targetField": "ToField",
"targetTable": "ModifyingTable",
"project": "MyProject"
}
RenameMappingTable looks like...
FromField | ToField |
Row | New Row |
Row ID | New Row Id |
Order ID | New Order Id |
Order Date | New Order Date |
Shipping Date | New Shipping Date |
Shipping Mode | New Shipping Mode |
Customer ID | New Customer Id |
Customer Name | New Customer Name |
Segment | New Segment |
City | New City |
State | New State |
---------------------------------------------------------------------------------------------
Variable Browser (shift for local)
Variables within Variables allowed
Variables "Preview" [to check Variables within Variables ok]
VerifyNullOrMissingData (+mode="INFO" or "ERROR" default is INFO)
Added optional description to RequiredFields
----------------------------------------------------------------------------------------------
MapFieldNames
MapMissingFields
----------------------------------------------------------------------------------------------
Bug Fixed in auto-adjust data size for BYTE double fields.
Bug Fixed templates not applied to single element reports in server size ExecuteReport to file.
----------------------------------------------------------------------------------------------
Distinct counting added to Profile and MultiFunction Profile.
4 x Performance increase in Quantile function.
Auto Index Dashboard builder accessed from Model menu
Customizable plugins added to Explorer and accessed through Model menu
Added JSON key "separator" to CombinedDimension function. (override underscrore)
if %DSTAMP% found in variable it will be replaced at the start of script
Data Audit: Export to Table - produces an Auto Dashboard (definable)
----------------------------------------------------------------------------------------------
Data Audit: Export to Table
Drag field onto MFP/Change function has better name handling.
In a Multi Field Profile; if there are a maximum of 2 labels across all the fields they will be considered a collection of binary values and coloring will be identical. e.g. ("0","1")
Tip:
Newly saved (4.4.29.2+) Multi Field Reports will have, in their"controlState" entry a maxBinaryColumns key (NB. appears twice per report, change both)
"controlState": {
"preferredChart": "Stacked",
"plotNull": false,
"maxBinaryColumns": 2
}
Increase this value, when you know all fields are related, to get identical series colors in bars and pie.
In the following case maxBinaryColumns is set to 4
4.4.28.1
EXTEND | WildICast: if source is numeric field then also work |
FEATURE | Let some expression string function work with numbers lcase() ucase() trim() |
BUG | Excel load handling "Big" Integers |
EXTEND | GetDetails: Use RadGridView |
EXTEND | Allow "skipIfMissingSourceTable" for all export modes |
BUG | Export into Workbook UI not passing all params |
EXTEND | Partial Implementation of forced forcedTypes: In Export Workbook |
Check (in script) if a field is entirely null
{
"method": "IF",
"condition": "count([table.field],null) == count([table.field],Total)",
"project": "D3"
}
4.4.24.1
FEATURE | Chart option - Multi Line Series as Multi Axis |
FEATURE | Better Cartesian chart legend margins |
FEATURE | Chart option - reverse categorical axis |
FEATURE | LoadTableFromExcel: treat 1899-12-30 as 1900-01-01 |
4.4.22.1
BUG | Programmed Field - Edit in script: No Window Title |
BUG | Stop Date field allowed to be declared integer |
FEATURE | Replace multiple graph option buttons with combo box |
FEATURE | Add more graph options |
FEATURE | Small table nDimensional profile alternate algorithm |
4.4.21.1
BUG | DAYSBEFOREMAX(A) - date time field |
FEATURE | More variation on first/last crosstab panel |
CHANGE | replace tabDelimited Export with delimiter |
BUG | Wild Decode Duplicates tied together |
FEATURE | Preview button on Profile/Fprofile/IProfile reports |
FEATURE | Execute Report - Any Standard Report |
FEATURE | Slack Integration - Hard Coded channels |
FEATURE | Execute Report - Upload ( for discovery) |
FEATURE | Export Into Table : adjustSymmetry |
BUG | Append to table:double generic |
ExportIntoTable
If appending, adding "adjustSymmetry": true will add new fields, preloaded with nulls up to the current number of records.
[
{
"method": "ExportIntoTable",
"table": "asymtable",
"append": false,
"fields": [
"customer.cid"
],
"project": "RegressionDB"
},
{
"method": "ExportIntoTable",
"table": "asymtable",
"append": true,
"adjustSymmetry": true,
"fields": [
"customer.cid",
"customer.gender-code"
],
"project": "RegressionDB"
}
]
IF "condition" can be "count([table.field],value">0" etc. Examples:
count([customer.gender],Male) != 0
count([customer.gender],Female) > 100
count([customer.gender],Male) > count([customer.gender],Female)
if needed, the value can be enclosed in escaped quotes
"condition" : "count([customer.gender],\"Male\") > count([customer.gender],\"Female\")"
Support for UTF8 filenames
IF "condition" can be "true" or "false" (and hence form a conditional execution, especially useful with variables).
ExportIntoTable (and Script Editor JSON Menu) implements skipIfMissingSourceTable:true/false.
CreateTableFromWorkbook, accepts a string as "headerRow":"%HeaderRow%".
Generally, API calls that would normally accept a single int, long, double or bool will now take a string in its place - making them all usable in variables.
Prototype IF condition in scripts. Example:
[
{
"method": "IF",
"condition": "%StartRow%==0 && %EndRow%==0",
},
{
"method": "ELSE",
},
{
"method": "ENDIF",
}
]
CreateTableFromWorkbook: "worksheet" : "*" to load first worksheet.
modules.json in bin folder to load and register external modules
{
"modules": [
{
"name": "ESSServer",
"args": "",
"port": 6000
}
]
}
Simpler set of data types in field definitions.
"Auto Growing" discrete data types, loading or appending.
If issues arise, add "oldformats=1" to server startup batch file
{
"method": "CreateAuditTable",
"sourceTable": "DATA_RAW",
"ETLTable": "PREPARE_EXPORT",
"outputTable": "ETLAuditTable",
"suffix": "OLD",
"orderBy": "RAW",
"outputAllETL": true,
"project": "DEMO_20220331_183955",
"mapping": [
{
"source": "COMPANY ID",
"target": "Company ID"
},
]
}
Updated Regular Expression Helper in Wild Decode
Update Push Programmed Field code to library
CreateTableFromWorkbooks has "order" key. can be WRITTEN or CREATED(default)
String fields that contain integers with leading "0"'s will be preserved on export into workbook
CreateTableFromWorkbooks
{
"method": "CreateTableFromWorkbooks",
"table": "superstore-batch",
"path": "%DATAPATH%ExcelLoad/files",
"worksheet": "Transactions",
"headerRow": 1,
"filenameColumn": "Filename",
"worksheetRowName": "SheetRow",
"project": "ExcelFileLoad"
}
Will create table and load all xlsx files from location "path", from the worksheet names in "worksheet"
Optional "filenameColumn" will create and populate a column of that name with the short filename of each source file.
Optional "worksheetRowName" will create and populate a column of that name with the worksheet row number of each record loaded.
Supports the optional keys found in CreateTableFromWorkbook. ( minDiscreteSize, leadingZeroAsStringType etc)
CreateTableFromWorkbook
- Implemented action="APPEND"
- Specify initial minimum discrete field size with key "minDiscreteSize": 1[2 or 4]
- Added key "fileRowName", if present will add a column with this name containing the short filename of workbook
CreateTableFromWorkbook added "leadingZeroAsStringType" flag (default False)
ErrorIfAnyNullOrZero shows detail in Script Editor.
Will preserve empty slots in dashboard
Explorer & server-side Data Audit now includes table
Additional Regular expression in wild decode
ExportIntoTable: added key "minDiscreteSize" : 1 (2 or 4)
Fix string field in Expression returning two NULL values.
ExportTo Workbook UI in DataView window - added bottom right micro-button to access.
Push Python Routine now collects optional parameters.
Programmed Fields can now be referenced by name, with optional parameters.
Much Improved UI for new RegEx wild decode.
key "round": False(True) added to WildICast string function
View Export Templates in management console.
Function crosstab honours show null columns when filtered.
Display nulls as zero in function profiles and crosstabs.
Late addition
There is no UI, but add the key "regex":true to the JSON and the wild decoder will use regular expressions. e.g.
(This includes the Excel Version)
"(?=.*BELTED)(?=.*SHORTS)" contains BELTED and SHORTS
"(?=.*BELTED)(?=.*SHORTS|.*SKIRT)" contains BELTED and (SHORTS or SKIRT)
Experimental - contains wild checkbox on Custom Decode - Excel Tab.
The Wild decode has been slightly enhanced so you can enter things like:
BADMINTON && BOOT
BADMINTON && SHOE
(so just repeat that section, once for BOOT and once for SHOE)
You can use multiple && and || but not mixed yet
Will be extending this so we can do
SHOE || BOOT && BADMINTON
Edit Programmed Field in script.
Better reporting of json parse errors in script editor.
Recording of Python errors returned in API.
cnfg file allows specification of Python executable
Browser Incomplete request check
Post/Response debug tools
Added signature key to postman
e.g.
{
"method": "Postman",
"subject": "Regression Complete",
"body": "Regression load is complete",
"to": [
"tim.e.mccarthy@gmail.com",
"tmccarthy@datajetsoftware.com"
],
"attach": [
"%DATAPATH%RegressionTest/output/BLACK.xlsx",
"%DATAPATH%RegressionTest/output/BLACK-stacked.xlsx",
"%DATAPATH%RegressionTest/output/Audit.xlsx"
],
"reportLinks": [
"Styled Engagement Dashboard",
"Styled Survey Dashboard"
],
"signature": "\r\n\r\nFrom Tim\r\n\r\n",
"project": "RegressionDB"
}
- add StopErrorLog before Postman
Mail file lock released
Improvements to Script Editor 'Find' behaviour
{
"method": "StartErrorLog",
"filename": "ErrorLog.txt",
"mailTo": [
"tim.e.mccarthy@gmail.com"
],
"workbook": {
"filename": "e:/log.xlsx",
"append": false,
"variables": "Variables",
"info": "Info",
"errors": "Errors"
},
"project": "MyProject"
}
Added Stats Model entry for null results
Fixed (HTTP) response for Unicode characters.
Programmed Field can execute library function by name, with optional parameters.
File export now has Check Box option to over write existing file.
Successive Export Reports can append worksheets to the same workbook.
Experimental Explorer dashboard styling now included fonts
Improved Stats panel behaviour/UI , added count of unique values in a field.
Allow empty DataSets in Stats Panel filter
Improved Stats Panel UI experience.
Partially Calculating Stats Panels
Fixed bug where stats panel entry could no longer be modified
Stats Panel: allows count and unique on text fields
Stats Panel: allows count and unique on text fields
Script 'Log' written to (now expandable) Lower log window
ExportIntoWorkbook: added key "emptyNullCells" : true(false)
if true null cells will not have a "" written to them. default is false (previous bevaviour)
UTF-8 support (requires reload)
Fix to ETLAudit "method" : "sample"
New method of writing to Excel
{
"method": "ETLAudit",
"table": "customer",
"targetTable": "audit-table",
"entries": [
{
"type": "Decode",
"method": "compare",
"source": "gender-code",
"target": "gender",
"detail": "decoding gender field"
},
{
"type": "Decode",
"method": "compare",
"source": "occupation-code",
"target": "occupation",
"detail": "decoding occupation field"
},
{
"type": "Custom Replace",
"method": "compare",
"source": "status",
"target": "status-replaced",
"detail": "replacing 'ed' with 'r'"
}
],
"project": "MyProjecy"
}
Explorer only, prototype dash board styling - second from right button in micro toolbar.
- E.G. try 16, 0, check off, check on,+ pick a colour
Palette Setting in script...
{
"method": "AddToCollection",
"overwrite": true,
"name": "LookAndFeel",
"description": "",
"type": "Settings",
"collection": "ProjectSettings",
"definition": {
"chartPalette": [
"#2E8FFF",
"#1Ed2dd",
"#1fb864",
"#ffc500",
"#ff7c1f",
"#ed3434",
"#e63cb5",
"#a530f2"
]
},
"project": MyProject
}
(Experimental) option on some charts to show null values. [new button in micro-toolbar]
Project manager - Delete multiple projects
Project Manager - Grid Sizes reduced in line with other grids
Extended JSON helpers in Script Editor (CreateTableFromWorkbook, ExportIntoWorkbook)
CreateTableFromWorkbook - asText boolean key
Extended support for Discovery Report preview
ExecuteReport can accept a dataSet in root of JSON
API: user snippet Add/Delete/List
API: snippet refresh from GitHub
Editable Postman, increase dialog size
Last Discrete query function
Create Fields from Values updated.
Programmed Field Now has personal code repository.
String function CTOD(A): if possible, make a discrete copy of reduced 'continuous string' field A, by applying a filter.
Backup MongoDB API
String function CONCAT now obeys filter.
Toggle State and Time columns in Script Editor for editing....
Explorer Only update - Script Editor
- Remarks now edit in a text window with a title and description.
- Remarks with a title starting *** are shown as bold dark blue in the editor.
- These *** remarks are 'bookmarks' and appear as locations on the Right-Click->Goto menu
Report Tree & List view search UI as Database
API Check Python is installed
Expression: str() function (string from numeric)
Expression: operators < <= > >= on strings
Core Support for Experimental DJFScanProc
Object name restrictions relaxed, see Field and Table Names
Report tree now has search capability similar to Database tree
Added library feature to Programmed Field UI
Create a new field by applying a function written in Python Programmed Field
Example for Material Processing:
Script entry
{
"method": "BuildBakedField",
"targetTable": "materials",
"overwrite": true,
"name": "Processed Material",
"function": "PyDiscreteProc",
"source": "Material",
"code": "696D706F7274207379730A0A20200A696E70757466696C65203D207379732E617267765B315D0A6F757470757466696C65203D207379732E617267765B325D0A0A6946696C65203D206F70656E28696E70757466696C652C202272222920236F70656E73207468652066696C6520696E2072656164206D6F64650A776F726473203D206946696C652E7265616428292E73706C69746C696E65732829202370757473207468652066696C6520696E746F20616E2061727261790A6946696C652E636C6F736528290A0A202020200A0A6F46696C65203D206F70656E286F757470757466696C652C20277727292023777269746520746F2066696C650A666F72206C696E6520696E20776F7264733A0A20202020230A2020202070726F636573736564203D2022220A20202020736570203D2022220A20202020746F6B656E73203D206C696E652E73706C697428272027290A20202020230A20202020230A2020202073746172744174203D20300A202020207768696C65202873746172744174203C206C656E28746F6B656E7329293A0A2020202020202020696620286E6F7420746F6B656E735B737461727441745D2E5F5F636F6E7461696E735F5F2827252729293A0A20202020202020202020202073746172744174202B3D20313B0A2020202020202020656C73653A0A202020202020202020202020627265616B0A20202020230A202020206C61737450657263656E74546F6B656E203D20737461727441740A20202020230A20202020696620286C656E28746F6B656E732920213D2073746172744174293A0A2020202020202020232374203D20737461727441740A2020202020202020666F72207420696E2072616E676528737461727441742C6C656E28746F6B656E7329293A232020283B2074203C206C656E28746F6B656E73293B20742B2B293A0A202020202020202020202020746F6B656E203D20746F6B656E735B745D0A20202020202020202020202069662028746F6B656E2E656E6473776974682827252729293A0A20202020202020202020202020202020696620287420213D2073746172744174293A0A202020202020202020202020202020202020202070726F636573736564202B3D207365700A2020202020202020202020202020202020202020736570203D20222C20220A2020202020202020202020202020202020202020666F72206C20696E2072616E6765286C61737450657263656E74546F6B656E2C74293A2320666F7220286C203D206C61737450657263656E74546F6B656E3B206C203C20743B206C2B2B293A0A202020202020202020202020202020202020202020202020696620286C20213D206C61737450657263656E74546F6B656E293A0A2020202020202020202020202020202020202020202020202020202070726F636573736564202B3D202220220A20202020202020202020202020202020202020202020202070726F636573736564202B3D20746F6B656E735B6C5D0A20202020202020202020202020202020202020206C61737450657263656E74546F6B656E203D20740A2020202020202020230A2020202020202020696620286C61737450657263656E74546F6B656E20213D206C656E28746F6B656E7329293A0A20202020202020202020202070726F636573736564202B3D207365700A202020202020202020202020736570203D20222C20220A202020202020202020202020666F72206C20696E2072616E6765286C61737450657263656E74546F6B656E2C6C656E28746F6B656E7329293A23666F722028696E74206C203D206C61737450657263656E74546F6B656E3B206C203C20743B206C2B2B293A0A20202020202020202020202020202020696620286C20213D206C61737450657263656E74546F6B656E293A0A202020202020202020202020202020202020202070726F636573736564202B3D202220220A2020202020202020202020202020202070726F636573736564202B3D20746F6B656E735B6C5D0A202020202020202023202020202020202020202020202020200A2020202020202020202020206C61737450657263656E74546F6B656E203D20740A202020200A20202020230A202020206F46696C652E77726974652870726F6365737365642E72737472697028292B225C6E22290A6F46696C652E636C6F736528290A",
"project": "MyProject"
}
Python Code
import sys
inputfile = sys.argv[1]
outputfile = sys.argv[2]
iFile = open(inputfile, "r") #opens the file in read mode
words = iFile.read().splitlines() #puts the file into an array
iFile.close()
oFile = open(outputfile, 'w') #write to file
for line in words:
#
processed = ""
sep = ""
tokens = line.split(' ')
#
#
startAt = 0
while (startAt < len(tokens)):
if (not tokens[startAt].__contains__('%')):
startAt += 1;
else:
break
#
lastPercentToken = startAt
#
if (len(tokens) != startAt):
##t = startAt
for t in range(startAt,len(tokens)):# (; t < len(tokens); t++):
token = tokens[t]
if (token.endswith('%')):
if (t != startAt):
processed += sep
sep = ", "
for l in range(lastPercentToken,t):# for (l = lastPercentToken; l < t; l++):
if (l != lastPercentToken):
processed += " "
processed += tokens[l]
lastPercentToken = t
#
if (lastPercentToken != len(tokens)):
processed += sep
sep = ", "
for l in range(lastPercentToken,len(tokens)):#for (int l = lastPercentToken; l < t; l++):
if (l != lastPercentToken):
processed += " "
processed += tokens[l]
#
lastPercentToken = t
#
oFile.write(processed.rstrip()+"\n")
oFile.close()
RenameField is logged
Script Editor Paste/Delete behaviour improved
ProcessMaterials now has an function PROCMAT(A) in Function Fields
CreateTableFromWorkbook
Added ability to add column containing source worksheet row number, using key worksheetRowName
UI has been updated.
{
"method": "CreateTableFromWorkbook",
"action": "LOAD",
"table": "superstore",
"filename": "%INFILE%",
"worksheet": "Transactions",
"headerRow": 1,
"worksheetRowName": "ExcelRow",
"project": "MyProject"
}
ProcessMaterials
{
"method": "BuildBakedField",
"targetTable": "table containing source field",
"overwrite": true,
"name": "short name of new field",
"function": "string",
"p1": "ProcessMaterials",
"p2": "short name of source field",
"project": "MyProject"
}
LogWrite
Accessed through Script Editor Tools menu.
{
"method": "LogWrite",
"text": "text to write to log",
"project": "MyProject"
}
Handles templates with Periods in header names.
Allows '&' in field names.
ExportIntoWorkbook supports iterator and dataSet
Example project (copy zip into %DATAPATH%\ExcelLoad and extract.
{
"method": "ExportIntoWorkbook",
"filename": "%OUTFILE%.xlsx",
"xlTemplate": "%TEMPLATE%",
"startRow": 3,
"sheet": "owid",
"includeHeaders": false,
"exportTemplate": "MyExportTemplate",
"iterator": {
"field": "processed.Org Ship Mode",
"includeNull": false
},
"project": "ExcelFileLoad"
}
Extension to ExcelInOut example