JSON EDL Advanced

Excel data location (EDL) may define an array of excel cells. For this purpose, boolean JSON attribute IS_ARRAY is set to true (if missing, default value is false). Accordingly, fields: ROW_INDICES and COLUMN_INDICES are used to define arrays of row and column indices. JSON keywords ROW_INDEX and COLUMN_INDEX are always needed regardless of the use/non-use of attributes ROW_INDICES and COLUMN_INDICES (this is a JSON syntax redundancy for these EDL cases).

Since all row/column indices are given explicitly it is not needed to be consequent numbers, hence more complex sets of excel cells are gathered in the array in this way. Column indices can be set in two ways: as integers (i.e. indices as numbers) or as column labels (as used in the Excel standard cell addressing). For example: "COLUMN_INDICES" : [2,3,4] or "COLUMN_INDICES" : ["B","C","D"] are both valid specifications. Also for continues and large sets of indices, an alternative string based syntax is also allowed e.g. "COLUMN_INDICES" : "2-40".

IS_ARRAY option is needed to define variables which are arrays (see previous section Variables).

Cell values combination

EDL attribute SOURCE_COMBINATION may be set true to define a piece of information obtained as a combination of the data from different columns and rows. All data sources are concatenated into a single text value. If needed, attribute COMBINATION_SEPARATOR may be used to set a separator string for the source combination concatenation.

Examples for concatenating the values of two cells with keyword SOURCE_COMBINATION:

{
	"SOURCE_COMBINATION" : true,
	"IS_ARRAY" : true,	
	"ITERATION" : "ABSOLUTE_LOCATION",
	"COLUMN_INDEX" : "A",
	"COLUMN_INDICES" : ["A","C"],
	"ROW_INDEX" : 10   
}

The above EDL will produce string which a concatenation of the values of cells A10 and C10 i.e. "Cell(A10)Cell(C:10)".

Keyword, "COMBINATION_SEPARATOR" : "--", could be added and as a results following string will be obtained: "Cell(A10)--Cell(C:10)"

{
	"SOURCE_COMBINATION" : true,
	"IS_ARRAY" : true,
	"COMBINATION_SEPARATOR" : "--",
	"ITERATION" : "ABSOLUTE_LOCATION",
	"COLUMN_INDEX" : "A",
	"COLUMN_INDICES" : ["A","C"],
	"ROW_INDEX" : 10   
}

The cells could be combined in a reverse order (or any order if more than two cells are specified) e.g. following results will be obtained : "Cell(C10)--Cell(A:10)"

{
	"SOURCE_COMBINATION" : true,
	"IS_ARRAY" : true,
	"COMBINATION_SEPARATOR" : "--",
	"ITERATION" : "ABSOLUTE_LOCATION",
	"COLUMN_INDEX" : "A",
	"COLUMN_INDICES" : ["C","A"],
	"ROW_INDEX" : 10   
}

Combination of different sources

The defined block of cells (or grid of cells if the indices are not consequent) could be combined with the values stored in variables. For this purpose JSON attribute VARIABLE_KEYS is used.

{
	"SOURCE_COMBINATION" : true,
	"IS_ARRAY" : true,
	"COMBINATION_SEPARATOR" : "--",
	"ITERATION" : "ABSOLUTE_LOCATION",
	"COLUMN_INDEX" : "A",
	"COLUMN_INDICES" : ["A","C"],
	"ROW_INDEX" : 10,
	"VARIABLE_KEYS" : ["var1", "var2"]   
}

The above JSON configuration will concatenate cells A10 and C10 together with the values stored in variables var1 and var2 e.g. the result string will be in the form: "Cell(C10)--Cell(A:10)--var1--var2"

Accordingly var1 and var2 must be defined in JSON section VARIABLES.

Additionally value defined directly in the JSON section of an EDL could be added to a source combination e.g.:

{
	"SOURCE_COMBINATION" : true,
	"IS_ARRAY" : true,
	"COMBINATION_SEPARATOR" : "--",
	"ITERATION" : "ABSOLUTE_LOCATION",
	"COLUMN_INDEX" : "A",
	"COLUMN_INDICES" : ["A","C"],
	"ROW_INDEX" : 10,
	"VARIABLE_KEYS" : ["var1", "var2"],
	"JSON_VALUE" : "json-val"   
}

The obtained combined string will look like: "Cell(C10)--Cell(A:10)--json-val--var1--var2". The order of source combination (string concatenation) is ARRAY + JSON_VALUE + VARIABLES.

Mapping

The value defined by an ordinary EDL or via combination of sources could be transformed (mapped) using JSON attribute "MAPPING" e.g. Cell(A10) --> new mapped value:

{	
	"ITERATION" : "ABSOLUTE_LOCATION",
	"COLUMN_INDEX" : "A",	
	"ROW_INDEX" : 10,
	"MAPPING" : "TimeMapping"
}

In order to work, for the above JSON config example, a mapping named "TimeMapping" must be defined in section VARIABLE_MAPPINGS. Particular mapping is defined by means of two variables of the type ARRAY. The first array is a key set and the second one contains the values (see previous section Variables). The original excel cell value is replaced with corresponding mapped value i.e. the cell value is treated as a 'key' and accordingly the value for this 'key' is loaded instead of the original cell value (or instead of the obtained source combination value).

"VARIABLE_MAPPINGS": [
	{
		"NAME": "TimeMapping",
		"KEYS_VARIABLE": "TimeDesignations",
		"VALUES_VARIABLE": "TimePoints"
	}
]

Next: Effect Blocks Advanced