Blending known fund balances and changes

[tim6467]

I'm working with two data sets related to a number of funds...

Table 1 is a list of transactions related to the fund: (Date|Value|Fund|Description) where deposits and withdrawls are listed.

Table 2 is known balances of the funds, taken at random dates (Date|Fund|BalanceOnDate)

I would like to combine the data sets to calculate gains/losses on the funds that are not masked by deposits and withdrawls.

I've tried using running totals, unions and many other methods but with no success.

Any help appreciated.

Many thanks

Tim

[jdunkerley79]

Try the attached.

- Joins the two together (using a Join Multiple) as an Outer Join and merge into a single table

- Sort into Fund, Date and then use a Multi Row to compute balances when null

- Finally fill in the Revaluation changes

[jrgo · Accepted solution]

@tim6467,

Slightly different approach than @jdunkerley79 by using the UNION tool to blend vs. a Join Multiple. 

[jdunkerley79]

Union probably a better solution I would expect. But would want to ensure processing of revaluation was done in correct sequence if a Deposit/Withdrawl and revaluation occurred on same day

Can't say which way round would be correct without more business knowledge

[jrgo]

Very true! I’m sure either of these will give him a better idea to solve.

[tim6467]

Both thank you very much. I'm now being defeated by having an older version of the Designer.

I'm on v11.0.3 waiting for work to upgrade us.

Is it possible to share a backwards-compatible version? Or failing that just configuration window screenshots of the processes?

Huge thanks,

Tim

[jdunkerley79]

I’m not in front of Alteryx but if you open the file in notepad and change the yxmdVer to 11.0 then should open fine.