Download2
  • 20 Minutes to read
  • Dark
    Light
  • PDF

Download2

  • Dark
    Light
  • PDF

Article summary

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  

 
STANDARDIZE(A)
NORMALIZE(A)
NORMALIZE@ZERO(A)
MEANCENTRIC(A)
NORMAL(AB)
LOGNORMAL(AB)
CHISQUARED(A)
CAUCHY(AB)
POISSON(A)
BINOMIAL(AB)
BERNOULLI(A)
UNIFORMI(AB)
UNIFORMR(AB)


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"
}
]



---------------------------------------------------------------------------------------------

RequiredFieldsFromTable

---------------------------------------------------------------------------------------------

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..

 

NewFieldType
Created RowSTRING
Created Row IDSTRING


MapFieldNamesFromTable

{
  "method": "MapFieldNamesFromTable",
  "sourceTable": "RenameMappingTable",
  "sourceField": "FromField",
  "targetField": "ToField",
  "targetTable": "ModifyingTable",
  "project": "MyProject"
}

RenameMappingTable looks like...


 

FromFieldToField
RowNew Row
Row IDNew Row Id
Order IDNew Order Id
Order DateNew Order Date
Shipping DateNew Shipping Date
Shipping ModeNew Shipping Mode
Customer IDNew Customer Id
Customer NameNew Customer Name
SegmentNew Segment
CityNew City
StateNew 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

EXTENDWildICast: if source is numeric field then also work
FEATURELet some expression string function work with numbers lcase() ucase() trim()
BUGExcel load handling "Big" Integers
EXTENDGetDetails: Use RadGridView
EXTENDAllow "skipIfMissingSourceTable" for all export modes
BUGExport into Workbook UI not passing all params
EXTENDPartial 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

FEATUREChart option - Multi Line Series as Multi Axis
FEATUREBetter Cartesian chart legend margins
FEATUREChart option - reverse categorical axis
FEATURELoadTableFromExcel: treat 1899-12-30 as 1900-01-01

4.4.22.1

BUGProgrammed Field - Edit in script: No Window Title
BUGStop Date field allowed to be declared integer
FEATUREReplace multiple graph option buttons with combo box
FEATUREAdd more graph options
FEATURESmall table nDimensional profile alternate algorithm

4.4.21.1

BUGDAYSBEFOREMAX(A) - date time field
FEATUREMore variation on first/last crosstab panel
CHANGEreplace tabDelimited Export with delimiter
BUGWild Decode Duplicates tied together
FEATUREPreview button on Profile/Fprofile/IProfile reports
FEATUREExecute Report - Any Standard Report
FEATURESlack Integration - Hard Coded channels
FEATUREExecute Report - Upload ( for discovery)
FEATUREExport Into Table : adjustSymmetry
BUGAppend 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

  1. Implemented action="APPEND" 
  2. Specify initial minimum discrete field size with key "minDiscreteSize": 1[2 or 4]
  3. 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.

ExportIntoWorkbook

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

  1. Remarks now edit in a text window with a title and description.
  2. Remarks with a title starting *** are shown as bold dark blue in the editor.
  3. 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

Create Fields from Values

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"
}


Replace Function

Log Changes

Extension to ExcelInOut example


Was this article helpful?