This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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".
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.
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and reboot. Order shall return.
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.