dynamically search dataset depending on input from second file

[Y_B]

Hi everyone,

I'm new to Alteryx and seek some advise on the following issue. I want to search a dataset dynamically depending on input from a second file or a third file (or more).

Example: 

Dataset

Numeration	Product	FVM LEVEL
1	Bond	1
2	Bond	1
3	Derivative receivable	3
4	Derivative receivable	2

second file

Name	Decision_Variable	Contains
Derivative	Product	Deriv

third file

Name	Decision_Variable	Contains
Level 1	FVM LEVEL	1

Second and third file are 2 sheets from the same excel workbook and I know how to run through via macro. The following dynamic search should be included in said macro.

In this example, when the second file is "active", I would like the workflow to search in field [Product] (Decision_Variable of second file) of the dataset for entries that contain "Deriv". A new field [Cluster] should be added to the dataset that contains "Derivative" ([Name] second file), if cell in the same row and column [Product] contains "Deriv".

When the third file is "active" the workflow should search in field [FVM Level] (Decision_Variable of thrid file) of the dataset for entries that contain 1. If found add "Level 1" to new column [Cluster].

Result from first second file:

Numeration	Product	FVM LEVEL	Cluster
1	Bond	1
2	Bond	1
3	Derivative receivable	3	Derivative
4	Derivative receivable	2	Derivative

Result from third file:

Numeration	Product	FVM LEVEL	Cluster
1	Bond	1	Level 1
2	Bond	1	Level 1
3	Derivative receivable	3
4	Derivative receivable	2

I have tried to solve this by appending second/third file with the Append Fields tool as new columns to the dataset and creating column "cluster" with an if statement in a formula tool, but how to address the columns in the dataset dynamically depending on the Decision_Variable.

Thanks for you help in advance,

Y_B

[Amol_Telore · Accepted solution]

Hey @Y_B 

Here is my solution. Hope this what you are looking for.

[ArtApa]

Hi @Y_B - Here is how you can do this. Pink containers are identical, but Right Inputs are different. Therefore they give different results, as per your request:

[Y_B]

Thanks for your replies!

@ArtApa The second formula tool you used in each of the container would need manual adjustments if I were to change e.g. cluster names. So I went with @Amol_Telore solution.

Using this flow with a bigger dataset has led me to an issue that I'll explain with the following example:

Assume second and third files as before and the following change to the dataset:

Numeration	Product	FVM LEVEL
1	Bond	1
2	Bond	1
3	Derivative receivable	1
4	Derivative receivable	2

Now #3 would have two Clusters, Derivative and Level 1. Problem is that I only want to have one cluster and that should be the cluster that is defined "first", which means in this case it would be the one from the second file (if I had 4 files and 2 Clusters from third and forth file were assigned, it would be the cluster from the third file).

In @Amol_Telore solution the last cross tab is listing the cluster names alphabetically (correct me if I'm wrong). 

[Y_B]

Solved above by adding 1_ , 2_ etc. to cluster names.

In a last stepp I am trying to remove those prefixes with the regex tool. I tried regular expressions ^(.*?)_ and ([^_]*).*. The last one gives back the prefix only. What would be the correct expression here?

Thank you,

Y_B

[Amol_Telore]

Hey @Y_B 

I have modified the workflow a bit to take only first cluster for each group based on first come first serve basis. PFA workflow for your reference. Hope this solves your last concern as well.

Regards,

Amol Telore

[Y_B]

Hi,

This is the output for the changed dataset (input) from above. As you can see there are 2 clusters. Did you maybe accidentally upload the old workflow?

Best regards,

Y_B

[Amol_Telore]

Hey @Y_B 

My bad. While uploading it to the portal I choose older flow. PFA new process. 

Regards,

Amol Telore

[Y_B]

Thank you very much