community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Performing Calculation Across Rows Conditionally

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

Alteryx Certified Partner
Alteryx Certified Partner

@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 reboot. Order shall return.
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

 

Bolide

@TimeTravel_0,

 

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

 

Best,

MSalvage

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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 reboot. Order shall return.
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

Alteryx Certified Partner
Alteryx Certified Partner

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 reboot. Order shall return.
Meteoroid

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

Alteryx Certified Partner
Alteryx Certified Partner

@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 reboot. Order shall return.
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