Using formula based on table data

[Davasu]

Hi

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

[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

[atcodedog05]

Hi @Davasu 

Does the number of rows and columns vary? 

[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