Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Performing Calculation Across Rows Conditionally

TimeTravel_0
6 - Meteoroid

I have a table with 3 fields, one of which is an ID that the data is grouped by.  What I want to do is use the other fields to perform a calculation based on their values. So the fields I have are ID, Price, and New_Price. I want to perform the logic: if isNull([New_Price]) then [Price] else [New_Price] endif across all records, and have the field names for this formula be based on ID: "Final_Price_ID1", "Final_Price_ID2", ... , "Final_Price_ID28".

 

 

 

 

Thanks,

Time_Travel_0

11 REPLIES 11
MarqueeCrew
20 - Arcturus
20 - Arcturus

@TimeTravel_0

 

It took me a few reads to get this.  As I think you are looking for something like this:

 

Output 1 record of:

 

Final_Price_ID1|Final_Price_ID2|Final_Price_ID3|Final_Price_IDN

999.99|999.99|999.99|999.99

 

Can you please confirm this?

 

Thanks,

Mark

 

 

 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
TimeTravel_0
6 - Meteoroid

Hey @MarqueeCrew, thanks for the response. What I mean is; say this is my original table:

 

ID|Price|New_Price

1|2.40|[null]

2|3.20|4.30

3|1.60|[null]

 

What I want to see as output is:

 

ID|Price|New_Price|Final_Price_ID1|Final_Price_ID2_|Final_Price_ID3..........

1|2.40|[null]|2.40|[null]|[null]

2|3.20|4.30|[null]|4.30|[null]

3|1.60|[null]|[null]|[null]|1.60

 

MSalvage
11 - Bolide

@TimeTravel_0,

 

One way to do this would be with the Cross Tab tool. Sample workflow attached.

 

Best,

MSalvage

MarqueeCrew
20 - Arcturus
20 - Arcturus

@TimeTravel_0,

 

I've taken liberties with @MSalvage's solution.  My workflow is a simplification of it and generates the output that you desired (I think).  

 

Note:  This isn't to say that my solution is better, it is just another way of looking at it.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
TimeTravel_0
6 - Meteoroid

Sorry, I thought a simple representation of the data would make it easier, but my data is ACTUALLY like this: 

 

ID|Price1|Price2|Price3|New_Price1|New_Price2|New_Price3

1|2.40|2.22|2.89|[null]|[null]|2.78

2|3.20|2.90|2.80|4.30||2.22|[null]

3|1.60|2.70|2.28|[null]|1.56|[null]

 

So the output I want would be:

 

ID|Price1|Price2|Price3|New_Price1|New_Price2|New_Price3|Final_Price_ID1|Final_Price_ID2_|Final_Price_ID3

1|2.40|2.22|2.89|[null]|[null]|2.78|2.40|2.22|2.78

2|3.20|2.90|2.80|4.30||2.22|[null]|4.30|2.22|2.80

3|1.60|2.70|[null]|[null]|1.56|[null]|1.60|1.56|2.28

 

So the logic is: IF isNull([New_PriceX]) THEN [PriceX] ELSE [New_PriceX] ENDIF AS Final_Price_IDX

MarqueeCrew
20 - Arcturus
20 - Arcturus

the dynamic nature of the data presents a challenge.  is there a fixed number of prices?  3?  or is it variable.

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
TimeTravel_0
6 - Meteoroid

Yes, a fixed number. There are 26. I was hoping to be able to do it dynamically rather than many if statements.

MarqueeCrew
20 - Arcturus
20 - Arcturus

@TimeTravel_0,

 

This was tricky.  Here's a picture:

 

capture.png

 

Warning! When things look tricky, I expect someone to post an easier way that I end up kicking myself about.

 

The reason why this is tricky is that we are collecting an array of dollar amounts as the Final_Price.  We then want to burst that array into 26 fields where  the name of the field is the ID# and not the numbers 1 - 26.  In our example data, we have 1-3.  I don't presume that to be the final solution values.  You've got a history of simplifying the example.  

 

What we did was join the data to itself and filter down the data so that we see ID, Price, New Price on a single row for each set of prices.  Then we calculate the final price.  we then concatenate all of the prices into a FP field.  Note, if the ID is "Hotel" and there are no values until Price 3, you'll end up with:  ,,9.99 

 

Now we parse the FP field into 26 fields and are almost done.  The problem is that the parse will # the fields from 1-26.  We take the original input and create a FP_# map to the Final_Price_[ID] to convert the field names.  Now we're essentially done.

 

Please try the attached workflow.

 

Cheers,

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
MSalvage
11 - Bolide

@TimeTravel_0,

 

I went with a cross tab/transpose solution should get you what you want. It will result in only the final prices but if you still desire the whole table you can just join it back to the original on ID.

 

Best,

MSalvage

 

Edit: Added \d+ in regex for more that 9 prices

 

 

Labels