Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Condition Join? Join how?..... I'm just lost....

michael_franz
8 - Asteroid

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.

 

 

9 REPLIES 9
jdunkerley79
ACE Emeritus
ACE Emeritus

I would suggest you generate all the month values valid for each employee.

 

Once you have the month end dates it is easy to use a join to link it all together.

 

Have attached a sample of what I tried

michael_franz
8 - Asteroid
I like the thought. But as u can see from the sample. I'm needing to conditionallly join base on the person being primary. Any help there
jdunkerley79
ACE Emeritus
ACE Emeritus

You can filter the Personnel data to just the IsPrimary set. This didnt match the sample data you provided quite but hopefully gets you close enough to get there.

 

Attach updated workflow

michael_franz
8 - Asteroid

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

 

  • I would do that for the Exec and Broker......

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......

 

 

 
michael_franz
8 - Asteroid

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.

jdunkerley79
ACE Emeritus
ACE Emeritus

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

 

2017-06-07_08-21-43.jpg

 

Sample attached

 

michael_franz
8 - Asteroid

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...

 

 

 

 

jdunkerley79
ACE Emeritus
ACE Emeritus

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.

michael_franz
8 - Asteroid

Awesome! A work of Art... thank you so much... Now on to the next hurdle of the project!

 

Labels