community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

data transformation to show all Possible combinations

Meteor

I have data which shows availability for a period. It has multiple relationships.

FIELD1FIELD2TIME1TIME2
APP1BAN11/1/20195/30/2019
BAN1APP21/1/20195/30/2019
APP3BAN11/1/20195/30/2019
BAN1CAN11/1/20193/30/2019
CAN2BAN13/30/20195/30/2019

 

I need to transform the data to create table which shows the relationship between all in the list. 

For example, BAN1 is available on all APP's from 1/1/19 to 5/30/19.

                      CAN1 is available on BAN1 from 1/1/19 to 3/30/19 and CAN2 is available on BAN1 from 3/30/19 & 5/30/19

 

My output should be give me all possible combinations like this.

APPBANCANTIME1TIME2
APP1BAN1CAN11/1/20193/30/2019
APP3BAN1CAN11/1/20193/30/2019
APP2BAN1CAN11/1/20193/30/2019
APP1BAN1CAN23/30/20195/30/2019
APP3BAN1CAN23/30/20195/30/2019
APP2BAN1CAN23/30/20195/30/2019

 

Can someone help me on how to proceed?

Alteryx Certified Partner

Hi @hash_89 

 

My first thought when looking at the problem was that Make Group would be the right tool to use. Thinking about it more your problem may be a bit more complex than that but I thought I'd mention it in case it moves you a step closer to a solution.

Meteor

Hi, 

 

Thank you for the suggestion. I was able to use this tool to generate the groups and key, but i have trouble in proceeding further.

 

Group Key
APP1 APP1
APP1 APP2
APP1 APP3
APP1 BAN1
APP1 CAN1
APP1 CAN2

 

I can see that this helps in connecting what is available on what, but I'm not able to figure out how to proceed further.

Alteryx Certified Partner

Ok, I think I've got it all figured out in the attached example solution. Creating the unique combination list was a little tricky since I wanted to keep it dynamic and not hard code to any of the value from this input. I uses an iterative macro with a batch macro inside that.

 

Beyond that, the logic appears to be correct as my workflow replicated your desired results. Let me know if you have any questions.

 

Meteor

 

Thankyou very much for the workflow. I can see that it is giving me the possible combos. 

 

APP BAN CAN
APP1 BAN1 CAN1,CAN2
APP2 BAN1 CAN1,CAN2
APP3 BAN1 CAN1,CAN2

 

How do i get the date ranges to these combinations?

 

For instance, APP1 BAN1 CAN1,CAN2 is actually

APP1BAN1CAN11/1/20193/30/2019
APP1BAN1CAN23/30/20195/30/2019
Alteryx Certified Partner

The attached workflow should produce those dates as a part of the final table. Check out the image below. Is this what you were looking for, or is another output desired?

 

20190517-AllCombosDates.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Meteor

I have an older version. When i imported the packages, i had errors when running them. I couldn't see this portion of it.Can you please send me the screenshot of the connections of this workflow?

 

Capture.PNG

 

Alteryx Certified Partner

That's odd. This workflow was built in 2018.3. The batch macro goes there with "Name" as the control parameter and no grouping fields. I've verified that the package has all the necessary files. I'll reattach in just in case.

 

20190517-AllCombosDates2.png

Labels