Row merge with logic

[#1]

Hi,

I would like to be able to transform some data which includes some aggregation and a group by.

The data I have consists of 2 rows of data that have the same ID but some different attribute values. The main requirement I am trying to achieve is the ability to take say Column A's 2 rows and transpose them out into a single row, but with some logic applied. At the same time I would like to Group the two rows and Sum one of the other values.

I have attached a simple example file that shows the starting data and the target with some notes.

If anyone can help in pointing out the right artifacts to use and approaches that would be great.

James

[#2]

Again, many thanks John.

Now that I have put my coding hat back on I can see how this works, this great example of mixing Code with data.

So it's cracked, I managed to get the aggregate to output the values I was expecting and yes I see how you can just use the index pointer in the data table to get the values you after. I can see why the Sum would'nt help me, that's just me thinking of SQL again..

I have also added the downstream aggregator and added all the columns as the group by and I get the single row output I was after.

This is a great example for anyone interested the aggregate script is below which can be used to obtain specific row in a group and perform some aggregation, worth noting that you specify the Group By values in the artifacts properties in the dataflow window. 

[#3]

The code in your latest version of the result function is not as rich as the code in your prior version.  Are you sure you are doing all the analysis that you wanted?  Don't you need to compare the values in Type and SpotPrice from both records to determine the value to be assigned to SwapType?  Or can you make that decision based just on the values in the first record of each pairing?  I don't understand the logic behind setting the SwapType value to Pay Fixed, Receive Fixed, Pay Floating, and Receive Floating.  The logic of your decision function doesn't match the required result presented in the data file.

[#4]

The result I currently get isn't exactly perfect, I was more interested in getting the whole aggregation and calculation working rather than the logic behind the Swap Type, in the previous script I had been playing with variations of the decision function to see results.

Yes, I still need to do a check to decide the swap type, I will implement that shortly, the logic behind the Type selection hasn't changed , but the required result has, in all cases each record the Fixed part takes precendent.

So every grouped Swap will be xxx Fixed , xxx being Pay or Receive depending on the sign of the price.

I have implemented this using the code below, I've tested this and it works.

output.SwapType = decision (

work[1].Type == "Fixed" and work[1].SpotPrice <0,"Pay Fixed",

work[1].Type == "Floating" and work[1].SpotPrice <0,"Receive Fixed",

work[1].Type == "Fixed" and work[1].SpotPrice >0, "Receive Fixed",

work[1].Type == "Floating" and work[1].SpotPrice >0, "Pay Fixed"

)

[#5]

OK, much clearer. 

Nice use of the decision function.  As this code illustrates, the proper decision is made regardless of which of the two records per swap is stored in work[1].

Thanks for this example.  It shows a nice usage of the Aggregate operator function rule.