Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Data need to be filtered and sum market value and shares based on Transaction value

  1. #1

    Question Data need to be filtered and sum market value and shares based on Transaction value

    Hi Friends!!

    Good Evening..


    I am looking for the solution where ,I can do calculation on the below data.

    Entity_Id Account_Name Trans_Code Total_Share Market_Value
    10001 Growth Fund Buy 100 1000
    10001 Growth Fund Sell 100 1000
    10002 Aggressive Fund Buy 1300 13000
    10002 Aggressive Fund Sell 450 4500
    10003 Income Fund Buy 250 2500
    10004 Brokerage Fund Buy 200 2000
    10004 Brokerage Fund Sell 100 1000


    and convert it into the below output through spoon and how?
    Please help me I am new to pentaho...

    Entity_Id Account_Name Total_Share Market_Value
    10001 Growth Fund
    10002 Aggressive Fund 850 8500
    10003 Income Fund 250 2500
    10004 Brokerage Fund 100 1000

    Please find the attachment.
    Attached Images Attached Images

  2. #2
    Join Date
    Feb 2013
    Posts
    530

    Default

    Hi,

    I have given a trail on this thread and got the output as you expected.

    Design flow of ETL
    ----------------------
    output.jpg

    Download KTR
    --------------------
    test.ktr

    I hope this helps you :-)

    Cheers..!!!
    Last edited by sadakar; 05-25-2015 at 03:43 PM.
    - Sadakar Pochampalli

  3. #3
    Join Date
    Apr 2008
    Posts
    4,374

    Default

    Sadakar,

    I think you might have missed the use-case.
    Add in one more transaction, to show how your data-flow still works:

    10004 Brokerage Fund Buy 50 500

    Abhshek.Mishra, Sadakar has given you one of the tools you could use for this: Switch Case, though I would likely have used a Value Mapper, mapping "Buy" to +1, and "Sell" to -1
    Then multiply the Total Share & Market Value to this "Accounting Direction", and use the Group By step to calculate an overall total.

    This is a LOT easier to read, and can be supported longer term.
    Last edited by gutlez; 05-25-2015 at 02:50 PM.

  4. #4
    Join Date
    Feb 2013
    Posts
    530

    Default

    Hi Gutlez,

    I have edited the TEXT file with
    10004 Brokerage Fund Buy 50 500

    in place of
    10004 Brokerage Fund Buy 200 200

    and ran the transformation, resulted with Negative values.

    I have assumed Buy >= Sell in sample data provided ( and Sell may not present for few "Entity_ID"s).

    My solution might not be generic but I'll try to go with your advice :-)

    Thank you.
    - Sadakar Pochampalli

  5. #5
    Join Date
    Apr 2008
    Posts
    4,374

    Default

    Sadakar,

    I was more indicating to *ADD* an extra row of data, not modify the existing row.
    You solution will fail with multiple "Buy" or "Sell" records in the data set.

    Using the flow layout I suggested will allow as many "Buy" or "Sell" records in the data as the user wants.

  6. #6
    Join Date
    Feb 2013
    Posts
    530

    Default

    Yes, it fails. (added an extra row and Tested).

    Will try in suggested way.

    Thank you.
    - Sadakar Pochampalli

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2017 Pentaho Corporation. All Rights Reserved.