Due to recent updates, all users are required to create an Altair One account to login to the RapidMiner community. Click the Register button to create your account using the same email that you have previously used to login to the RapidMiner community. This will ensure that any previously created content will be synced to your Altair One account. Once you login, you will be asked to provide a username that identifies you to other Community users. Email us at Community with questions.

Aggregate using the same set

DivyemDivyem Member Posts: 17 Contributor II
in Help
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.
Tagged:

Best Answer

Answers

  • hbajpaihbajpai Member Posts: 102 Unicorn
    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.
    Best,
    Harshit
  • MarcoBarradasMarcoBarradas Administrator, Employee, RapidMiner Certified Analyst, Member Posts: 272 Unicorn
    @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
  • DivyemDivyem Member Posts: 17 Contributor II
    @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.
  • DivyemDivyem Member Posts: 17 Contributor II
    @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
  • BalazsBaranyBalazsBarany Administrator, Moderator, Employee, RapidMiner Certified Analyst, RapidMiner Certified Expert Posts: 955 Unicorn
    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
  • DivyemDivyem Member Posts: 17 Contributor II
    @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. 
  • DivyemDivyem Member Posts: 17 Contributor II
    anyone with better solution here please?
  • DivyemDivyem Member Posts: 17 Contributor II
    Closing this call 
Sign In or Register to comment.