Need optimal Solution Ideas

Hello, I have 2 input files and the expected output is also attached. I am not sure how to approach to this solution. I did it old way with using formula tool for each reason code/unSecured code but there are 100s of reason codes , I only attached few in the input sheets. So, is there a easier way to achieve this using Alteryx. Please reach out if you need additional information.

InputData has the information of client and their unsecured reason codes.

UnSecuredMasterSheetInputData2 has the rules behind each reason code.

Expected outputdata is it will use input data and join UnsecuredMasterSheetInputData2 on reasoncode and bring up all the other fields. I am able to stack my data in rows but not able to flip them into the "expectedOutputdata" format.

InputData.xlsx

ExpectedOutputData.xlsx

UnSecuredMasterSheetInputData2.xlsx

Output

Tips and Tricks

Join

Optimization

Interface Tools

Text Mining

Accepted answers

jrlindem

@sannapareddy

Okay so a couple things worth pointing out:

Careful when you’re uploading excel files where you’ve removed a bunch of data. The unsecured file was about 36mg and had a bunch of extra metadata still lurking around in the file that you probably don’t want “out there”. I removed all that so my solution file was smaller
The concept of needing to pivot out the data into section of repeating fields… I wasn’t sure if you needed help on that as part of your solution so I threw together a crude proof of concept (attached) to show you how you can accomplish that but I didn’t organize or refine the output since I’m not as familiar with the data

The main part of your ask was around the crosswalk. And if I’m interpreting your ask correctly, my earlier response I think still has merit. The “unsecured” file essentially is the crosswalk. You can reference that file on the side like my workflow shows and join it back in so you don’t need to manage that part (red box and arrow).

(also attached)

But my suspicion based on you pointing out the hundreds of formulas is that you’re trying to solve for the Parameter 1 and Parameter 2 logic and actually use the contents of those fields as your calculations?

If you broke those fields into the comparator and the comparison value, you could create an IF statement like this to handle all the calc’s dynamically:

IF [Comparator] = ">" THEN [Value] > [Compare_Value]

ELSEIF [Comparator] = "<" THEN [Value] < [Compare_Value]

ELSEIF [Comparator] = "=" THEN [Value] = [Compare_Value]

ELSEIF [Comparator] = ">=" THEN [Value] >= [Compare_Value]

ELSEIF [Comparator] = "<=" THEN [Value] <= [Compare_Value]

ELSEIF [Comparator] = "!=" THEN [Value] != [Compare_Value]

ELSE NULL()

ENDIF

Would take a little effort to build out, but I think it’d be worth while considering the lift you’re up against.

Hope this helps, and good luck! -Jay

Sample_Solution_Packaged.yxzp

KGT

So, you have the input file, and you have a ruleset. You need every one of those rules run against the InputData? This is the example that the Dynamic Formula (CReW Macros) was built for. Basically, you can create each of those rules into formulas and pass them as a bulk set against the Input data. There's an example of that prep in the Blue container.

I've provided some ideas in the attached for how you may do it independently, but it's not a finished solution as there are several ways to go depending on what you require. This shows how to evaluate one by one, however I've only completed one of the condition formulas. I built it off the response by @jrlindem as he had already prepared the inputs and so I didn't have to include the larger file and trimming steps.

As for the actual format of the output, that leads me to think that you want to evaluate each independently, but it's getting the results first and then work out how to match that format from here.

Sample_Solution_Packaged - KGT.yxzp

All comments

jrlindem

Admittedly haven't looked at the files, but in concept, wouldn't it be easier to use a crosswalk table with the mapped values so you can maintain those values outside of the workflow or formula tool? You would then JOIN them inside the workflow or use FIND REPLACE to swap values out.

sannapareddy

@jrlindem, can you please throw in a sample workflow, I dont think the crosswalk table would work as my table will have field names and the values are coming from the actual column headers coming from Input Data and each reason code uses a different set of column headers for the values to be extracted.

jrlindem

@sannapareddy Ya, I can take a deeper look later tonight and offer some more targeting recommendations. Sit tight, otherwise, others from the community might also jump in. -Jay

jrlindem

@sannapareddy

Okay so a couple things worth pointing out:

Careful when you’re uploading excel files where you’ve removed a bunch of data. The unsecured file was about 36mg and had a bunch of extra metadata still lurking around in the file that you probably don’t want “out there”. I removed all that so my solution file was smaller
The concept of needing to pivot out the data into section of repeating fields… I wasn’t sure if you needed help on that as part of your solution so I threw together a crude proof of concept (attached) to show you how you can accomplish that but I didn’t organize or refine the output since I’m not as familiar with the data

(also attached)

ELSEIF [Comparator] = "<" THEN [Value] < [Compare_Value]

ELSEIF [Comparator] = "=" THEN [Value] = [Compare_Value]

ELSEIF [Comparator] = ">=" THEN [Value] >= [Compare_Value]

ELSEIF [Comparator] = "<=" THEN [Value] <= [Compare_Value]

ELSEIF [Comparator] = "!=" THEN [Value] != [Compare_Value]

ELSE NULL()

ENDIF

Would take a little effort to build out, but I think it’d be worth while considering the lift you’re up against.

Hope this helps, and good luck! -Jay

Sample_Solution_Packaged.yxzp

KGT

As for the actual format of the output, that leads me to think that you want to evaluate each independently, but it's getting the results first and then work out how to match that format from here.

Sample_Solution_Packaged - KGT.yxzp

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703