Iterative Macro? Challenging workflow design support

[ThankForYourHelp]

Hi there,

I am having a hard time building this in the Alteryx workflow. I tried to use the multi-row formula tool, but it seems like the logic is too complicated to just use the multi-row formula tool.

Please see the scenario logic below and examples (I'll have random data coming in and need to figure out the scenarios (Under Tag column) for those data. Group Number is more like a unique ID and should group the ID number before comparison). You cannot compare ID 1 to 555.

Final goal is that whenever there's new data, the tools should figure out what the scenario numbers are for those data.

I really appreciate your help. Let me know if you have any questions.

I need that Tag column:

Senario 1			Senario 2			Senario 3			Senario 4
Out	In		Out	In		Out	In		Out	In
A	B		A	B		A	B		A	B
A	C		B	A		B	C		B	B
A	D
more clarification from above senario
Senario 1			Senario 2			Senario 3			Senario 4
Out	In		Out	In		Out	In		Out	In
test	apple		test	apple		test	apple		test	apple
test	banana		apple	test		apple	banana		apple	apple
test	orange
Examples: (data will be like below) and need to figure out the tag for those data
Group Number	Out	In	Tag
1	test	apple	Senario 1 and Senario 2
1	test	apple	Senario 1 and Senario 2
1	apple	test	Senario 1 and Senario 2
1 (if just single one row)	apple	test	Senario 1
2	test	apple	Senario 1
555	test	apple	Senario 2 and Senario 3
555	apple	test	Senario 2 and Senario 3
555	test	banana	Senario 2 and Senario 3
777	test	apple	Senario 3
777	apple	banana	Senario 3

[KGT]

Although this is tempting, you won't need a macro for this. My solution below is very open, as I'm pretty sure this isn't your standard data format.

• Bring your data in (I've used the first 5 rows of your table).
• Format it vertically
• Attach the fruit names
• Format output and summarise

[ThankForYourHelp]

Hi KGT,

Thank you for helping me out, but the workflow you created is quite different from what I intended. I have modified my request to make it clearer for everyone to understand.

The purpose of this is to try to determine the scenario based on the incoming data, not to hard-code or rename those columns to "scenario."

For example, if there's data like "ABBA," then I should dynamically generate a new column called "Tag" and label it as "Scenario 2." There shouldn't be any hardcoded elements for the "Tag" column.

Thank you.

[KGT]

OK, so the top 2 sections are the same, but one has letters, and the other has fruits.. right?

The columns need names, so you can either rename them, or take the default names such as [Field2] or [F2].

So, the bottom section are actually not results from the top, but tests that get evaluated against the scenarios at the top? I'm not sure on the logic to get the results that you have. Banana is not present in Scenario 2 for instance.

If it is that the top are the scenarios, and then the bottom is the test data, then you need to format the top in a way to compare against. If the order of the components is important, then remove the sort tool, and you could just T2C the Concat_Fruit to 2 columns, or replace the summarise with a Cross-Tab on Scenario,RecordID. (would probably need to change Name to just In/Out).

[PangHC · Accepted solution]

@ThankForYourHelp  it confuse me
where group number 1 is valid if it not reusable (where extra test-apple),
but  in group number 555, it only valid when the pair is re-usable.

i draft a workflow where it re-usable.

result:

workflow:

Explain

Senario 2&3

1. join by group number and in - out (ensure not same row also)

group number	recordid	out	in	right_recordId	right_out	right_in
11	1	a	b	2	b	c
22	1	a	b	2	b	a

2. identify senario

    if [out]  = [right_in] = Senario 2

    if [out] != [right_in] = Senario 3

Senario 4

1. filter [out]=[in]

2. join back to see other rows where contains the [out]/[in] in above and it should have different value in another side.

Senario 1

if the group not fall under above. it senario 1.