Comparing Datasets With Unique Identifier - Stock Options Exercise Dates

[CGIQV]

Vague title for a complex problem.

There are two tables with data that's linked by a unique identifier, Option ID.  Table A represents when options were granted and other specific values related to these grant dates.  Table B represents a date when options were exercised.  

INPUT

TABLE A (Granted)

Option ID	Service Date	Total Options	Total Value
01234	02/01/2015	200	1,000
01234	02/01/2016	200	1,500
01234	02/01/2017	200	1,400
01234	02/01/2018	200	1,300

TABLE B (Exercised)

Employee ID	Option ID	Exercise Date	Total Options
98765	01234	05/21/2017	500

From these two tables we can see that Employee ID 98765 has been granted a total of 800 options.  On 5/21/2019 we see that 500 were exercised.

The goal is to find the sum of "Total Value" from Table A related to the exercising of these 500 options.

The rules in place would be as follows:

1. Exercised options will be attributed to the earliest service dates and count forward.

2. Each option at a specific date is equal in value.  100 options for 100 value on a specific date equals a value of 1 per option.

In this case, 500 exercised options would be attributed to:

Option ID	Date	Total	Total Value
01234	02/01/2015	200	1,000
01234	02/01/2016	200	1,500
01234	02/01/2017	100	700

The sum of Total Value is equal to 3,200.

The output would be very similar to Table B except instead of showing "Total Options" it should show the value related to those options.

OUTPUT

Employee ID	Option ID	Exercise Date	Total Value
98765	01234	05/21/2017	3,200

For regular usage, the input files will contain thousands of rows of data spanning numerous Employee IDs and Options.  The output would list all of the exercised options and values related to those.

[john_miller9]

@CGIQV ,

Here's one way to do it, using the generate rows tool.  Let me know if this works for you.

[CGIQV]

@john_miller9

At generate rows I'm getting:

Error: Generate Rows (7): Parse Error at char(0): Invalid type in operator <=.

[danilang · Accepted solution]

Hi @CGIQV 

@john_miller9 has a good solution, but since options can be granted by the millions, his technique of generating rows could balloon quite quickly

Here's a different take on it that uses the original data set without extra rows and is based around calculating a running total.

Dan

[CGIQV]

Worked perfectly.

I will now apply this to the bigger data set and see how it comes together.

Thanks for the assistance.