Thu Aug 16, 2018 6:50 am
Login Register Lost Password? Contact Us


Rolling up data into a set field.

Comments and questions related to the Enterprise Control Language

Tue Oct 10, 2017 3:39 pm Change Time Zone

Hello, I'm a new ECL developer and I'm trying to get a good handle on writing ECL efficiently.

I recently had something I was working on in which I had to create a set of data and then append that data into a new record structure. I figured out how to do it but I feel there's a better way to do so.

Right now I'm projecting a record set of phone numbers into a new structure that combines the phone numbers into a set based on the UniqueID and then Deduping that table, then combining the deduped table with another record set using a join.

Here's the proof of concept I came up with.
Code: Select all
//Some sample data
names := DATASET([{'B','Smith','015553'},
{'E','Smith','00041254'},
{'Br','Smith','015553'},
{'rwrr','Smith','342342342342'}], {STRING FName, STRING LName, STRING DID});


phonesStruct := {STRING Phone, STRING UniqueID};
phones := DATASET([{'9541241', '15553'},
                                    {'954444241', '41254'},
                                    {'1111', '15553'},
                                    {'2222', '15553'},
                                    {'3333', '41254'},
                                    {'4444', '41254'}
                                    ],
                                    phonesStruct);
                                    
//Define a structure for a table with the set of phone numbers.                                    
ProjectedPhonesStruct := {
   STRING UniqueID,
   SET OF STRING PhoneNums
};

//Define a function to return a set of phone numbers
SET OF STRING PhonesSet(dataset(phonesStruct) InRecs) := FUNCTION
   RETURN SET(InRecs, phone);
END;

//Define a transform to project the phones record into the new structure
ProjectedPhonesStruct PXForm(Phones L) := TRANSFORM
   SELF.PhoneNums := PhonesSet(Phones(UniqueID = L.UniqueID));
   SELF := L;
END;

//Project the phones
ProjectedPhones := PROJECT(Phones, PXForm(LEFT));

//Now remove duplicates, seems inefficient here?
DDPhonesAsSet := DEDUP(SORT(ProjectedPhones, (INTEGER)UniqueID), UniqueID);

//Define a transform to join the ProjectedPhones and Names recsets while omitting the redundant UniqueID field.
{names, SET OF STRING PhoneNums} CombinePhoneSetName(names L, ProjectedPhones R) := TRANSFORM
   SELF.PhoneNums := R.PhoneNums;
   SELF := L;
END;

//And finally JOIN them
JOIN(SORT(names, (INTEGER)DID), DDPhonesAsSet,
         (INTEGER)LEFT.DID = (INTEGER)RIGHT.UniqueID, CombinePhoneSetName(LEFT, RIGHT), LEFT OUTER);


I explicitly converted the UniqueId and DID fields to integers to emulate the real world layout in which they are both strings but one record set has leading zeros. I'm wondering if the INTFORMAT function is more efficient now that I wrote this up.

Thanks!
BGehalo
 
Posts: 12
Joined: Thu Sep 28, 2017 8:06 pm

Tue Oct 10, 2017 6:54 pm Change Time Zone

BGehalo,

Yes, the job can be done more simply. Here's the way I would approach this:
Code: Select all
J1Rec := RECORD
  RECORDOF(names);
  DATASET({phones.Phone}) PhoneNums;
END;
J1Rec J1XF(names L, phones R) := TRANSFORM
  SELF := L;
  SELF.PhoneNums := ROW({R.Phone},{phones.Phone});
END;
J1 := JOIN(names,phones,
           (INTEGER)LEFT.did = (INTEGER)RIGHT.UniqueID,
           J1XF(LEFT,RIGHT),LEFT OUTER);

J1Rec Roll1XF(J1Rec L, J1Rec R) := TRANSFORM
  SELF.PhoneNums := L.PhoneNums + R.PhoneNums;
  SELF := L;
END;
ROLLUP(SORT(J1,DID),
       LEFT.DID=RIGHT.DID AND LEFT.Fname=RIGHT.Fname,
       Roll1XF(LEFT,RIGHT));
Note that I'm creating a nested child dataset (covered in our Advanced ECL Part 1 course online) instead of the SET OF STRING that you had defined. This is a more "typical" ECL construct.

But if you really require a SET OF STRING, then this minor change (two lines, only) accomplishes that:
Code: Select all
J2Rec := RECORD
  RECORDOF(names);
  SET OF STRING PhoneNums;                    //this changed
END;
J2Rec J2XF(names L, phones R) := TRANSFORM
  SELF := L;
  SELF.PhoneNums := [R.Phone];                //and this changed
END;
J2 := JOIN(names,phones,
           (INTEGER)LEFT.did = (INTEGER)RIGHT.UniqueID,
           J2XF(LEFT,RIGHT),LEFT OUTER);
J2Rec Roll2XF(J2Rec L, J2Rec R) := TRANSFORM
  SELF.PhoneNums := L.PhoneNums + R.PhoneNums;
  SELF := L;
END;
ROLLUP(SORT(J2,DID),
       LEFT.DID=RIGHT.DID AND LEFT.Fname=RIGHT.Fname,
       Roll2XF(LEFT,RIGHT));

One general note: I see you're defining all your string fields as variable-length STRING. It's better practice, when you know the maximum size of a string field, to explicitly define it at that size. That produces more efficient runtime code. So I'd suggest changing your inline DATASETs to this:
Code: Select all
names := DATASET([{'B','Smith','015553'},
                  {'E','Smith','00041254'},
                  {'Br','Smith','015553'},
                  {'rwrr','Smith','342342342342'}],
                 {STRING4 FName, STRING5 LName, STRING12 DID});

phonesStruct := {STRING Phone, STRING12 UniqueID};
phones := DATASET([{'9541241', '15553'},
                   {'954444241', '41254'},
                   {'1111', '15553'},
                   {'2222', '15553'},
                   {'3333', '41254'},
                   {'4444', '41254'}],
                  phonesStruct);

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1369
Joined: Wed Oct 26, 2011 7:40 pm

Tue Oct 10, 2017 7:40 pm Change Time Zone

Thanks!
BGehalo
 
Posts: 12
Joined: Thu Sep 28, 2017 8:06 pm

Tue Oct 10, 2017 8:38 pm Change Time Zone

Playing around with stuff I also got it working by creating the set value during the join and then deduping the result.

Code: Select all
{RECORDOF (names), SET OF STRING PhoneNums} ItXForm(names L, phones R) := TRANSFORM
   SELF.PhoneNums := SET(phones((INTEGER)UniqueID = (INTEGER)L.DID), Phone);
   SELF := L;
END;

JoinedData := JOIN(SORT(names, DID), SORT(phones, UniqueID), (INTEGER)LEFT.DID = (INTEGER)RIGHT.UniqueID, ItXForm(LEFT, RIGHT), LEFT OUTER);
SortedData := SORT(JoinedData, DID, FName, LName);
DEDUP(JoinedData, DID, FName, LName);
BGehalo
 
Posts: 12
Joined: Thu Sep 28, 2017 8:06 pm

Wed Oct 11, 2017 5:36 pm Change Time Zone

BGehalo,

You do not need the SORTs in your JOIN:
Code: Select all
JoinedData := JOIN(names,phones,
                   (INTEGER)LEFT.DID = (INTEGER)RIGHT.UniqueID,
                   ItXForm(LEFT, RIGHT), LEFT OUTER);
because the internals of JOIN automatically handle that for you.

And your DEDUP versus my ROLLUP -- both accomplish the same thing, but ROLLUP calls a TRANSFORM function on the duplicate records making it a much more flexible solution than DEDUP.

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1369
Joined: Wed Oct 26, 2011 7:40 pm


Return to ECL

Who is online

Users browsing this forum: No registered users and 1 guest