Need to prompt for base month and comparison month associations

[A1]

I am working with historic (base) and forecasting (comparison) files. Each file has an ID and field names associated with months (Jan, Feb, Mar, etc.) that contain activity count values. The months present in each file can be as few as one, but no more than 12 - so there is no year/date information, just month. Often the identical months will be compared (Jan, Feb, Mar to Jan, Feb, Mar); however, there will be times when the historic months considered are JUN, JUL, AUG (say in 2020), but the comparison months need to be FEB, MAR, APR (say in 2021). I need to prompt the user to select which months in the base file should line up with which months in the comparison file.

How can this be accomplished?

Thanks in advance.

Base

ID	JUN	JUL	AUG
A1	15	15	15
A2	25	25	25
A3	5	10	15

Comparison

ID	FEB	MAR	APR
A1	15	15	25
A2	20	25	25
A3	5	15	10

[AZuc]

@A1 , I believe you're going to find what you need watching these videos: https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Creating%2...

[A1]

André,

Thanks for the link. I've gone thru the tutorial a couple of times in the past and can get the prompting to choose the months to use on each of the two files. What I'm having trouble with, and perhaps didn't explain well enough, is marrying up an individual base month with a comparison month. 

For example:  Both data sets may have the same 4 month values (Sep - Dec), but I may want to use only 3 months from each one - say Sep thru Nov from base and Oct thru Dec from comparison - attached could do that.

However, after selecting the months for consideration from each file, I want to do be able to do is say that the first month to consider in the base is Sep and that is to be matched with Oct in the comparison; subsequently Oct in base to be paired with Nov in comparison, and finally Nov in base to Dec in comparison. I suspect being able to create an output similar to the following is where I need to go; but I'm struggling to find a way to do this via prompting of the user. Since each base-comparison can be different in terms of number of months (up to 12 months) and the month-matching will not always be the same, a static input table cannot be used.

ID	Base	Comparison
1	Sep	Oct
2	Oct	Nov
3	Nov	Dec

Andy

[A1]

Appears I sent an early test where select tool fed by .yxdb files instead of crosstabs; and did not provide test input connections. Attached does so.

[AZuc]

@A1 ,

May I consider this format as the expected input format?

Base

ID	JUN	JUL	AUG
A1	15	15	15
A2	25	25	25
A3	5	10	15

Comparison

ID	FEB	MAR	APR
A1	15	15	25
A2	20	25	25
A3	5	15	10

[A1]

There are a lot more fields than just these; however, the input format for these fields and values are appropriate.

[AZuc]

@A1 

Would this format work for a prototype?

ID	Other Fields	FEB	MAR	APR
A1	X	15	15	25
A2	Y	20	25	25
A3	Z	5	15	10

[A1]

Yes

[AZuc]

@A1 , see if this workflow helps you.

Since the content is dynamic, I've opted to build a Chained App. The theory about it is at https://community.alteryx.com/t5/Interactive-Lessons/Chaining-Analytic-Apps/ta-p/243120

The other steps where basically to convert month strings into numbers and adjust the numbers in one of the sources.

[A1]

Thanks - hope to be able to play with it before too long