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

1. Junior Member
Join Date
May 2015
Posts
5

## 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?

 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

2. Senior Member
Join Date
Feb 2013
Posts
530
Hi,

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

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

--------------------
test.ktr

I hope this helps you :-)

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

3. Senior Member
Join Date
Apr 2008
Posts
4,448

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. Senior Member
Join Date
Feb 2013
Posts
530
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.

5. Senior Member
Join Date
Apr 2008
Posts
4,448

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. Senior Member
Join Date
Feb 2013
Posts
530
Yes, it fails. (added an extra row and Tested).

Will try in suggested way.

Thank you.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•