Aggregate using the same set

Divyem

I am trying to aggregate on the set itself on the basis of intra day amount happening. 
May be below query will help you understand my requirement more 

SELECT B.time, B.date, B.att1, SUM(A.COST)
FROM TBL1 B, TBL1 A
WHERE  A.time <= B.time
AND A.DATE = B.DATE AND A.att1 = B.att1
GROUP BY A.att1,A.att2,A.time, A.date;

I am not able to achieve it with any operator or multiple operators. Can anyone help me out on this.

BalazsBarany

Hi,

joining in the database *is* the better solution, you mentioned it yourself.

Regards,

Balázs

hbajpai

Hey @Divyem ,

The process you describe can be achieved using Pivot operator. Check out the operator and for sample usage you can refer to the help window in Studio.

MarcoBarradas

@Divyem I don´t know if your example set is already joined or not.
Since your join seems includes the =< operator you may need 
Database Envy operator available at the marketplace extension developed by @BalazsBarany

Divyem

@hbajpai I am trying to achieve something with =< which cannot be done on the pivot. I tried pivot but couldn't achieve the desired output.

Divyem

@MarcoBarradas that's an amazing finding for me, but there is one issue this operator gives me function to compare two attributes at once. I want to compare multiple attributes and merge the set together. taking some inputs from one set and aggregated inputs from other with some conditions as per the query above. I tried but couldn't come to a solution. Could you help me out in this

BalazsBarany

Hi @Divyem,

the manual way to do this is to preaggregate the second example set (self-joining the result back if necessary), then Cartesian Join with the first example set, then using Generate Attributes to calculate a "keep" column (true/false) using arbitrary complex expressions, and then filtering the result.

This is of course inefficient in memory and CPU terms but you have full control on the processing. It's good to avoid this approach if possible, but if it's not, this is how you have to do it.

Best regards,

Balázs

Divyem

@BalazsBarany so inshort if I have a set of 3 million rows this is impossible to do in rapidminer. Will have to link directly from the database with the respected query instead. Thanks for the inputs anyway sir. 

Divyem

anyone with better solution here please?

Divyem

Closing this call