Multikey sort with different sort criteria

CloverDX Platform
1

Hi,

I’m new to CloverETL and I’m evaluating it in order to know whether we can use it to port some mainframe JCLs to Java in an AIX platform.

We should be able to emulate some basical SORT statements:

INCLUDE COND, for filtering input records based in some hard-coded conditions.
SORT FIELDS, for sortering by some of the fields in the input layout.
OUTREC FIELDS, for creating an output layout different from the input layout.

Initially, it seemed to me that Clover should have components available for every one of the requirements above. I thought so … until I perceived that the EXT_SORT component allows defining several field keys but it forces to use only one sort order (ascending or descending) for all of them.

It is impossible, for example, to sort ascending by the first key and then for every value of the first key sort descending the different values of a second key.

One practical example:

00001;0004
00002;0003
00001;0001
00003;0001
00003;0002
00002;0001

I need to be able to get that sorted as follows:

00001;0004
00001;0001
00002;0003
00002;0001
00003;0002
00003;0001

Is it possible at all? Any ideas? I’m sure it should be possible, though maybe not as easy as I first expected.

I will apreciate any ideas. Thanks in advance.

2

Ok, I answer myself:

It seems the online documentation is not updated. For the EXT_SORT component we found the following:

Attribute Mandatory Description
sortKey yes field names separated by :;| {colon, semicolon, pipe}
sortOrder no one of “Ascending|Descending” {the fist letter is sufficient}

So it’s impossible to deduct that the component supports another different syntax, as I was able to find in the examples:

<Node enabled="enabled" guiHeight="0" guiName="Sort" guiWidth="0" guiX="174" guiY="34" id="EXT_SORT0" passThroughInputPort="0" sortKey="Code(a);Country(a);Currency(a);Amount(a)" type="EXT_SORT"/>

Great!!!

3

We suggest you better read our user`s manual:

http://www.cloveretl.com/_upload/clover … index.html

This possibility is described in this documentation.

You can find there, for example:

In its ExtSort section:

The resulting Sort key is a sequence of field names and an a or a d letter in parentheses separated by semicolon. It can look like this: FieldM(a);…FieldN(d).
In older versions of CloverEngine, the order was the same for all key fields and was specified using the Sort order attribute. Its default value was Ascending.

And in its Reference Part:

Sort key: Key according to which the records are sorted. Expressed as the sequence of individual expressions consisting of field name followed by the a or d letter in parentheses. Example: fieldName(a). These individual expressions are separated from each other by semicolon. Even the last individual expression can be followed by semicolon, however, this terminal semicolon is optional and can be omitted. Example of Sort key: first_name(a);last_name(a);salary(d). The sorting priority descends as the fields follow each other in the sequence.

However, we will update this issue on our wiki pages.

4

Hi twaller,

thanks for your info. I really appreciate.

Keep the good work, I’m really astonished at what can be done with Clover.

Best regards.