Using formula based on table data

I have some data which is arranged in a table in alteryx similar to the following

	1	2	3	4	5	6
1	NULL	10	20	30	40	60
2		NULL	10	20	30	45
3			NULL	10	20	30
4				NULL	10	25
5					NULL	20
6						NULL

the data represents the price so if you buy a 1 > 2 the price is 10 and if you buy a 1 > 6 the price is 60.

What I am then trying to check is if you buy a 1>2 and a 2>6 is this cheaper than a 1>6. In this case it would be 1>2 = 10 + 2>6 = 45 total 55 so it would be 5 cheaper.

I can do this in excel after creating the table as below, but is there a way to do this in Alteryx?

	1	2	3	4	5	6	DIFF
1	NULL	10	20	30	40	60	-5	-10	0	0
2		NULL	10	20	30	45		-5	5	5
3			NULL	10	20	30			10	10
4				NULL	10	25				5
5					NULL	20
6						NULL

Custom Tools

Best Practices

Batch Macro

Data Investigation

Iterative Macro

Accepted answers

All comments

Garrett_Stoker

Can you provide more information about the way you want the formula array to calculate? You gave an example for one cell, but it isn't clear what the other cells are meant to represent. If you could post an Excel file with your example, it would be easier to trace what the logic is.

My first thought would be this could use a Multi-Row formula, but an iterative macro may be needed depending on how the formulas look.

Davasu

Thanks for your response.

I have attached an excel file showing how I am looking to get the splits to show. I did think of a multi row formula but couldn't work out how it could do the individual cell choices.

A macro did seem as though it could work but I haven't used them in Alteryx

splits.xlsx

Davasu

Yes it will

CoG

So are you trying to identify:

1. Whether 1 step is cheaper than 2 step process (or vice versa) from 1 > n, where n is the maximum numbered column of your dataset?

2. The minimum cost path from 1 > n, where n is the maximum number of your dataset?

3. The minimum cost path for all purchases?

Davasu

It might be easier to think of it as a journey with intermediate calling points and the costs associated with those journeys.

So we have a cost for the full journeys 1>6 2>6 etc, but we also have a cost for the individual journeys 1>2, 2 >3 etc,

What I am trying to look at is if it is cheaper to buy 1>2 and 2>6 than 1>6 or is it cheaper to buy 1>3 and 3>6 than 1>6 for all the intermediate journey. And then the same for 2>6 would it be cheaper to buy 2>3 and 3>6 than 2>6 etc

Hope that helps

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703