Thu Jul 19, 2018 5:52 am
Login Register Lost Password? Contact Us


Grouping by criteria supplied at runtime.

Questions around writing code and queries

Tue Jul 03, 2018 5:01 pm Change Time Zone

Hi,

I have to count the number of records in a DATASET that match up to 5 criteria, the criteria themselves are held in another DATASET. So a Count is generated for every row in the 2nd DATASET. All the fields in the 2nd DATASET are individually nullable the only stipulation being that at least 1 criteria be supplied. By constructing a bitmap of the criteria present in every row of the 2nd DATASET one could do:
Code: Select all
JOIN(DATASET_1,DATASET_2,
        (RIGHT.bitmap = 1 AND LEFT.criteria_1 = RIGHT.criteria_1)
     OR (RIGHT.bitmap = 2 AND LEFT.criteria_2 = RIGHT.criteria_2)
     OR (RIGHT.bitmap = 3 AND LEFT.criteria_1 = RIGHT.criteria_1 AND LEFT.criteria_2 = RIGHT.criteria_2)
     OR (RIGHT.bitmap = 4 AND LEFT.criteria_3 = RIGHT.criteria_3)
     .
     .
     .
     OR (RIGHT.bitmap = 32....

But this seems VERY messy and there must be a neater way to do this.

Any ideas anyone?
Yours
Allan
Allan
 
Posts: 305
Joined: Sat Oct 01, 2011 7:26 pm

Wed Jul 04, 2018 8:53 am Change Time Zone

Actually found the solution, as the DATASET of criteria (dataset 2) is small I can do a JOIN ALL where the 2nd dataset can be on the RIGHT, copied to each node and compared against every record in the data DATASET (dataset 1), skipping those records that don't match that particular filter presented to the TRANSFORM.
Simplified the example down to 2 criteria, but holds just as well for my production code.
Code: Select all
RBucket := RECORD
    INTEGER id;
    INTEGER Account;
    STRING1 Brand;
END;

Bucket := DATASET([{1,0,'A'},{2,0,'C'},{3,10005,''},{4,10005,'B'}],RBucket);

RData := RECORD
    INTEGER id;
    INTEGER Tid;
    INTEGER Account;
    STRING1 Brand;
    REAL    Somedata;
END;

TL := DATASET([{0,10,10005,'A',1.7},{0,20,10007,'A',1.6},{0,30,10005,'B',2.0},{0,40,10008,'C',4.0},{0,50,10005,'C',1.8},{0,60,10007,'C',0.4},{0,70,10007,'A',0.1}],RData);

RData Doit(RData L,RBucket R) := TRANSFORM,SKIP(   (R.Account != 0 and R.Account != L.Account)
                                                OR (R.Brand != '' AND R.Brand != L.Brand))
    SELF.id := R.id;
    SELF := L;
END;

grp := SORT(JOIN(TL,Bucket,TRUE,DoIt(LEFT,RIGHT),ALL),id,tid);
grp;

RTABLE := RECORD
    INTEGER id := grp.id;
    INTEGER Cnt := COUNT(GROUP);
    REAL totalResponseTime := SUM(GROUP,grp.somedata);
END;

TABLE(grp,RTABLE,id);


Yours
Allan
Allan
 
Posts: 305
Joined: Sat Oct 01, 2011 7:26 pm


Return to Programming

Who is online

Users browsing this forum: No registered users and 0 guests