Please se the attached file. I am trying to join 2 workflows that I have mocked up. I created an "excel" solution so you have the end results that I am trying to achieve. Just not sure how to accomplish the final results.
Solved! Go to Solution.
UGH..... I see my way in not explaining myself clearly... For that let me apologize immediately..... I'm Sorry!
Let me explain a little more about the Personnel data. 99.9% of the polices are 12 months. 95% of the polices will have 1 Exec, 1 CSA, and 1 Broker for the entire 12 months. This is pretty easy for me to think about. Where I run into the issue is "IS PRIMARY"... See the field is not transactional. It is only on the person today. I lose the flag for determining who "was" primary if there was a change on the policy. A change on the policy is usually from someone terminating employment.
Lets look at the CSA. There are 3 of them... I recently added one to explain another challenge I am having... The 3 CSA's are Lisa, Karlie, and Smart. The "IS PRIMARY" = 'Y' flag is on Lisa in the file, but prior to Lisa coming onto the account 6/1/2017, Karlie would have been the Primary and the "IS PRIMARY" = 'Y' would have been attached to the record.
For each "Book of Business" month I am trying to create.... 1/1/2017, 2/1/2017.......12/1/2017..... There are ALWAYS... a Primary Exec, Primary CSA, and Primary Broker. There must be despite the "IS PRIMARY" = 'Y' changing and losing the history to join to.
In this example:
1/1/2017 - 5/1/2017 the Primary CSA is Karlie
6/1/2017 - 12/31/2017 the Primary CSA is Lisa
7/1/2017 - 12/31/2017 the Addt'l CSA is Smart.
I was thinking about passing a Is Primary='Y' only table through the workflow and attach everything. Then swing around again and pass Is Primary='N' only table through the workflow. But that would get me the results as follows
Is Primary='Y' Column
Jan-May [Null]
June - Dec Lisa
Is Primary='N' Column
Jan-May Karlie
June - Dec [Null]
Create a New Column = Final Primary CSA..... IF Is Primary='Y' Column is NULL then Is Primary='N' Column ELSE Is Primary='Y' Column
But!, yep always a but.... How to deal with the Addt'l CSA..... in this case it is Smart.........which I get lost again on....
Now I may have a theory, but I have no clue how to execute.
Please advise......
Hello
Attached is a different version of the workflow. I am close...I hope,..... I am having a problem bringing in the "enter date" from the original file. This will help me determine whom to plug into the primary "hole"...
See record line #1. House is Non Primary #1 and Mike is Non Primary #2...... Primary is <blank>... I need House or Mike to be the Primary for record line #1. So I'd create a new field using "IF then", but I need the enter date and the min. or first of the 2 (House or Mike) to fill the hole.
If I have understood the goal correctly I think this should do what you want.
- Do the join as you have it
- Order by Month (asc), PolId (asc), Type (asc), IsPrimary (desc)
- Use a sample tool to pick the first of each Month, PolId, Type group. This will pick in Primary = Y if present or one of the Primary = N if not
- Use a cross tab to create Type columns
Sample attached
WOW! I think that is going to work..... I have taken it one step further to get the "Other" employees one the report and not just the first (primary). Can I get your thoughts on the process.....I have taken your AWESOME thoughts and created 2 seperate thoughts to get to the same point I believe.... The top workflow I believe has a fault in not knowing how many people are on a policy. The bottom workflow is getting not only the primary but the second, third, ...... people on the policy at the specified book of business month......
Again, the primary is the primary where noted, but where the primary is not noted, I'm going based on the last enter date...
I think the simplest way to do what you want is to use a Multi Row formula to create an 'Employee Number' for each row where the first one is assigned 1 and then numbered down from there within each group.
Then remove the sample tool and crosstab them all.
Finally, a dynamic rename to make the name match what you wanted.
Sample attached.
Awesome! A work of Art... thank you so much... Now on to the next hurdle of the project!