Fri Dec 03, 2021 12:58 am
Login Register Lost Password? Contact Us


Performing multiple aggregations with different grouping.

Comments and questions related to the Enterprise Control Language

Wed Nov 14, 2018 10:00 am Change Time Zone

Hello,

I am trying to execute the following code:

filteredDS := DATASET([ {'abc','002','001','20181114','20181119'},
{'abc','001','001','20181114','20181119'},
{'abc','001','001','20181115','20181119'}],{string field_one,string field_two,string field_three, string field_four, string field_five});

Count_rec := Record
filteredDS;
INTEGER v_count := COUNT(GROUP(filteredDS,field_one,field_two,field_three));
END;

filteredDS_cnts := TABLE(filteredDS,Count_rec);
filteredDS_cnts;

Using the above code I wish to get the count based on the grouping provided in the layout "Count_rec". So as per my requirement, for first record i should be getting v_count = 1 and for second and third records I should be getting v_count = 2. But I do not get such counts and I get v_count = 3 for all the records.

Request your help in achieving this. I wanted to try this approach so that the actual number of records does not get reduced in my output "filteredDS_cnts". If the number of records get reduced due to group by clause then I may have to join back "filteredDS_cnts" to my original dataset "filteredDS".

Thanks and regards,
Akhilesh Badhri.
akhileshbadhri
 
Posts: 24
Joined: Thu Sep 22, 2016 12:15 pm

Fri Nov 16, 2018 6:34 pm Change Time Zone

Hi Akilesh,

This should get you there:
Code: Select all
filteredDS := DATASET([ {'abc','002','001','20181114','20181119'},
                        {'abc','001','001','20181114','20181119'},
                        {'abc','001','001','20181115','20181119'}],
                        {string field_one,
                       string field_two,
                       string field_three,
                       string field_four,
                       string field_five});

Count_rec := Record
INTEGER v_count := COUNT(GROUP);
END;

filteredDS_cnt1 := TABLE(filteredDS,Count_rec,field_one);
filteredDS_cnt2 := TABLE(filteredDS,Count_rec,field_two);
filteredDS_cnt3 := TABLE(filteredDS,Count_rec,field_three);

c1 := COUNT(filteredDS_cnt1);
c2 := COUNT(filteredDS_cnt2);
c3 := COUNT(filteredDS_cnt3);

d := DATASET([{'Field One Count:',c1},
      {'Field Two Count:',c2},
      {'Field Three Count:',c3}],
      {STRING20 valuetype,INTEGER val});

OUTPUT(d);


Regards,

Bob
bforeman
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1005
Joined: Wed Jun 29, 2011 7:13 pm

Mon Nov 19, 2018 3:54 pm Change Time Zone

Akilesh,

I think this is the best way to get what you want:
Code: Select all
filteredDS := DATASET([ {'abc','002','001','20181114','20181119'},
                        {'abc','001','001','20181114','20181119'},
                        {'abc','001','001','20181115','20181119'}],
                        {string field_one,
                       string field_two,
                       string field_three,
                       string field_four,
                       string field_five});

Count_rec := Record
  filteredDS;
  INTEGER v_count := 0;
END;

stbl := TABLE(filteredDS,
              {field_one,field_two,field_three,v_count := COUNT(GROUP)},
              field_one,field_two,field_three);

filteredDS_cnts := JOIN(filteredDS,stbl,
                        LEFT.field_one=RIGHT.field_one AND
                        LEFT.field_two=RIGHT.field_two AND
                        LEFT.field_three=RIGHT.field_three,
                        TRANSFORM(Count_rec,
                                  SELF.v_count := RIGHT.v_count,
                                  SELF := LEFT));
filteredDS_cnts;

HTH,

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


Return to ECL

Who is online

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

cron