Skip to main content

NORMALIZE

NORMALIZE(recordset, expression, transform [, UNORDERED | ORDERED( bool ) ] [, STABLE | UNSTABLE ] [, PARALLEL [ ( numthreads ) ] ] [, ALGORITHM( name ) ] )

NORMALIZE(recordset, LEFT.childdataset, transform [, UNORDERED | ORDERED( bool ) ] [, STABLE | UNSTABLE ] [, PARALLEL [ ( numthreads ) ] ] [, ALGORITHM( name ) ] )

recordsetThe set of records to process.
expressionA numeric expression specifying the total number of times to call the transform for that record.
transformThe TRANSFORM function to call for each record in the recordset.
childdatasetThe field name of a child DATASET in the recordset. This must use the keyword LEFT as its qualifier.
UNORDEREDOptional. Specifies the output record order is not significant.
ORDEREDSpecifies the significance of the output record order.
boolWhen False, specifies the output record order is not significant. When True, specifies the default output record order.
STABLEOptional. Specifies the input record order is significant.
UNSTABLEOptional. Specifies the input record order is not significant.
PARALLELOptional. Try to evaluate this activity in parallel.
numthreadsOptional. Try to evaluate this activity using numthreads threads.
ALGORITHMOptional. Override the algorithm used for this activity.
nameThe algorithm to use for this activity. Must be from the list of supported algorithms for the SORT function's STABLE and UNSTABLE options.
Return:NORMALIZE returns a record set.

The NORMALIZE function normalizes child records out of a recordset where the child records are appended to the end of the parent data records. The purpose is to take variable-length flat-file records and split out the child information. The parent information can easily be extracted using either TABLE or PROJECT.

NORMALIZE Form 1

Form 1 processes through all records in the recordset performing the transform function the expression number of times on each record in turn.

TRANSFORM Function Requirements for Form 1

The transform function must take at least two parameters: a LEFT record of the same format as the recordset, and an integer COUNTER specifying the number of times the transform has been called for that record. The resulting record set format does not need to be the same as the input.

NORMALIZE Form 2

Form 2 processes through all records in the recordset iterating the transform function through all the childdataset records in each record in turn.

TRANSFORM Function Requirements for Form 2

The transform function must take at least one parameter: a RIGHT record of the same format as the childdataset. The resulting record set format does not need to be the same as the input.

Example:

//Form 1 example
NamesRec := RECORD
  
UNSIGNED1 numRows;
STRING20 thename;
STRING20 addr1 := '';
STRING20 addr2 := '';
STRING20 addr3 := '';
STRING20 addr4 := '';
END;
NamesTable := DATASET([ {1,'Kevin','10 Malt Lane'},
{2,'Liz','10 Malt Lane','3 The cottages'},
{0,'Mr Nobody'},
{4,'Anywhere','Here','There','Near','Far'}],
NamesRec);

OutRec := RECORD
UNSIGNED1 numRows;
STRING20 thename;
STRING20 addr;
END;

OutRec NormIt(NamesRec L, INTEGER C) := TRANSFORM
SELF := L;
SELF.addr := CHOOSE(C, L.addr1, L.addr2, L.addr3,
             L.addr4);
END;
  
NormAddrs :=
            NORMALIZE(namesTable,LEFT.numRows,NormIt(LEFT,COUNTER));
/* the result is: numRows thename
            addr
1 Kevin 10 Malt Lane
2 Liz 10 Malt Lane
2 Liz 3 The cottages
4 Anywhere Here
4 Anywhere There
4 Anywhere Near
4 Anywhere Far */
//************************
//Form 2 example
ChildRec := RECORD
INTEGER1 NameID;
STRING20 Addr;
END;
DenormedRec := RECORD
INTEGER1 NameID;
STRING20 Name;
DATASET(ChildRec) Children;
END;

ds := DATASET([ {1,'Kevin',[ {1,'10 Malt Lane'}]},
{2,'Liz', [ {2,'10 Malt Lane'},
{2,'3 The cottages'}]},
{3,'Mr Nobody', []},
{4,'Anywhere',[ {4,'Far'},
{4,'Here'},
{4,'There'},
{4,'Near'}]} ],
DenormedRec);
ChildRec NewChildren(ChildRec R) := TRANSFORM
SELF := R;
END;
NewChilds := NORMALIZE(ds,LEFT.Children,NewChildren(RIGHT));

See Also: TRANSFORM Structure, RECORD Structure, DENORMALIZE