User Defined Field

Overview

Creates a new field, using Python to process the discrete values from the source field

To use this feature, Python must be installed on the DataJet server. 

For Windows see https://docs.python.org/3/using/windows.html

Coding

The code must 

• Input data from a file named in sys.argv[1] 
• Write the output to the file named by sys.argv[2]
• Optionally input counts from file named by sys.argv[3]

Hitting the test button will fill the right hand grid. Execution errors will also be displayed here.

The example above creates a field that is the first word of the occupation field.

import sys

#input and output files from first 2 arguments  
inputfile = sys.argv[1]
outputfile = sys.argv[2]
countsfile = sys.argv[3]

#read input file into array
iFile = open(inputfile, 'r') 
inputArray = iFile.read().splitlines() 
iFile.close()

print("There were "+str(len(inputArray))+" values")

oFile = open(outputfile, 'w') #write to file
for line in inputArray:
#
    try:
        #process the lines here
        tokens = line.split(' ')
        if len(tokens) > 0:
            processed = tokens[0]
        else:
            processed = ''
        
    except:
        processed="" #assign to null on exception
#endif    
#
    oFile.write(processed+'\n') 
oFile.close()
                        

The code is hex encoded in script, the entry for this example would be 

{
  "method": "BuildBakedField",
  "targetTable": "customer",
  "overwrite": true,
  "name": "occupation part 1",
  "function": "PyDiscreteProc",
  "source": "occupation",
  "code": "0A696D706F7274207379730A0A23696E70757420616E64206F75747075742066696C65732066726F6D206669727374203220617267756D656E747320200A696E70757466696C65203D207379732E617267765B315D0A6F757470757466696C65203D207379732E617267765B325D0A636F756E747366696C65203D207379732E617267765B335D0A0A237265616420696E7075742066696C6520696E746F2061727261790A6946696C65203D206F70656E28696E70757466696C652C2027722729200A696E7075744172726179203D206946696C652E7265616428292E73706C69746C696E65732829200A6946696C652E636C6F736528290A0A7072696E7428225468657265207765726520222B737472286C656E28696E707574417272617929292B222076616C75657322290A0A6F46696C65203D206F70656E286F757470757466696C652C20277727292023777269746520746F2066696C650A666F72206C696E6520696E20696E70757441727261793A0A230A202020207472793A0A20202020202020202370726F6365737320746865206C696E657320686572650A2020202020202020746F6B656E73203D206C696E652E73706C697428272027290A20202020202020206966206C656E28746F6B656E7329203E20303A0A20202020202020202020202070726F636573736564203D20746F6B656E735B305D0A2020202020202020656C73653A0A20202020202020202020202070726F636573736564203D2027270A20202020202020200A202020206578636570743A0A202020202020202070726F6365737365643D2222202361737369676E20746F206E756C6C206F6E20657863657074696F6E0A23656E646966202020200A230A202020206F46696C652E77726974652870726F6365737365642B275C6E2729200A6F46696C652E636C6F736528290A202020202020202020202020202020202020202020202020",
  "project": "MyProject",
  "forceString": false
}

Tips:

Right-Click->Edit Code in the Script Editor JSON window to make changes to code.

Right-Click->Edit in Solution Explorer Tree to modify a Programmed Field

The process will try to guess the created field's data type, check Prefer String Type to force a string field.

Use the Hide Empty Results check box to remove all rows with empty output from the result grid 

Library

The library tab gives access to example code snippets, copy all or part of this snippets to the code tab for use in your own function.

Hit the Try This button to test the snippet on your selected field.

Hit the Use button to copy the selected library code to Code tab.

Use Regular keyboard select, copy and paste to move all or part of the library code to the Code tab.

Library snippets can be found at https://github.com/datajetsoftware/PSnippets

Use the Combo Box above the library list to select System or User library.

The User Library list contains code snippets that have been developed by users in your realm.

To make a new entry hit the Push To Library button and supply a name and description.

User library entries can be deleted by right clicking on the entry in the list. You cannot delete or overwrite entries made by another user.

Use the Search Box in either the System or User library to display only entries with the types phrase in either the name or description.

Example

Copy the zip archive to the root of your %DATAPTH% and extract there. 

Load the "load.json" file in Script Editor and Run.

Video

See Extract JSON data with Python

Executing Library Function

The 'code' key can be replaced with a user library entry name in the key 'routine'

Parameters can be passed in an array with key 'params'

The following will Execute the user stored function GenericJsonExtract passing it the string "date" as a parameter.

{
  "method": "BuildBakedField",
  "targetTable": "actionreaction",
  "overwrite": true,
  "name": "packet-date-generic",
  "function": "PyDiscreteProc",
  "source": "json only packets",
  "routine": "GenericJsonExtract",
  "params": [
    "date"
  ],
  "project": "MyProject"
}

The first entry in params starts at sys.argv[4], and so on.

#set a default value for extract_key, for testing
extract_key = "sale"
#but if params is present then acquire new value for left
if len(sys.argv) >=5 :
    extract_key = sys.argv[4]

The Library Functions can now be accessed through the Engineering Menu->Programmed Field->Stored Routine.

The following will create a field using the "Top N" function, referencing by name - so any changes to the "Top N" routine will be 

reflected the next time the field is built.