Mon May 23, 2022 2:03 pm
Login Register Lost Password? Contact Us

Merge datasets without sorting issue

Comments and questions related to the Enterprise Control Language

Mon Oct 07, 2019 6:30 pm Change Time Zone

I am trying to produce a single file that has the direct flights first, then singles and then double flights. with in each section there is a sort
I dont want the sort order changed and the each section can be very large.

The output should be these 3 files should be appended together in the same order.

Here is what I tried
FormattedFlights := PROJECT(T4Seats,Schema.Layout);

All three files use the same record schema

// contains direct flights sorted
FormattedFlights1:= SORT(FormattedFlights(L2_departstationcode = ''),L1_DepartStationCode,L1_DepartTimein12hrsfmt);
// contains single connection flights sorted
FormattedFlights2:= SORT(FormattedFlights(L2_departstationcode <> '' and L3_departstationcode = ''),L1_DepartStationCode,L1_DepartTimein12hrsfmt);
// containds double connection flights sorted
FormattedFlights3:= SORT(FormattedFlights(L3_departstationcode <> '' ),L1_DepartStationCode,L1_DepartTimein12hrsfmt);

//Tried to just output but it looked like it merged per node
OutputFlights(FormattedFlights1+FormattedFlights2+ FormattedFlights1);

//Tried this but it resorted my output so I dont have distict directs,single and double flights
OutputFlights := MERGE(FormattedFlights1,FormattedFlights2,FormattedFlights3, SORTED(L1_DepartStationCode,L1_DepartTimein12hrsfmt));

//I tried MERGE without sort and I get a Warning Merge without an explicit SORTED() attribute is deprecated. But this did not give me the correct order either
OutputFlights := MERGE(FormattedFlights1,FormattedFlights2,FormattedFlights3);

I would appreciate any suggestions.
Posts: 8
Joined: Mon Oct 07, 2019 5:57 pm

Tue Oct 08, 2019 12:28 pm Change Time Zone


Did you try the Append operator (&) or the Prefix Append Operator ( (+) ) (
Code: Select all
//Try this:
OrderedFlights := FormattedFlights1 & FormattedFlights2 & FormattedFlights3;
//Or this:
OrderedFlights := (+)(FormattedFlights1,FormattedFlights2,FormattedFlights3);

And if that doesn't get you there, you can just do the whole thing against the entire dataset, like this:
Code: Select all
//add a flight type code
IsDirect := FormattedFlights.L2_departstationcode = '';      //Type 1
IsSingle := FormattedFlights.L2_departstationcode <> '' AND
            FormattedFlights.L3_departstationcode = '';      //Type 2
IsDouble := FormattedFlights.L3_departstationcode <> '';     //Type 3
TypedFlights := PROJECT(FormattedFlights,
                        TRANSFORM({UNSIGNED1 TypeCode,FormattedFlights},
                                  SELF.TypeCode := WHICH(IsDirect,IsSingle,IsDouble),
                                  SELF := LEFT));

//then SORT globally by TypeCode and the other sort fields
SortFlights := SORT(TypedFlights,TypeCode,L1_DepartStationCode,L1_DepartTimein12hrsfmt);
//then you can lose the TypeCode field, maintaining the sorted order
OrderedFlights := PROJECT(SortFlights,{FormattedFlights});

This uses WHICH to determine which flight type each record is, then you can just do a global SORT by that flight type and your two other sort fields. A simple PROJECT gets rid of the flight type field for the rest of your code.

You might want to detect if you end up with any TypeCode 0 records (which is possible if none of the WHICH expressions is TRUE) and handle that however you deem appropriate.


Community Advisory Board Member
Community Advisory Board Member
Posts: 1619
Joined: Wed Oct 26, 2011 7:40 pm

Thu Oct 31, 2019 4:13 pm Change Time Zone

Thank you that seemed to work
Posts: 8
Joined: Mon Oct 07, 2019 5:57 pm

Return to ECL

Who is online

Users browsing this forum: Google [Bot] and 1 guest