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
Solved! Go to Solution.
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
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
One way to do this would be with the Cross Tab tool. Sample workflow attached.
Best,
MSalvage
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
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
the dynamic nature of the data presents a challenge. is there a fixed number of prices? 3? or is it variable.
Yes, a fixed number. There are 26. I was hoping to be able to do it dynamically rather than many if statements.
This was tricky. Here's a picture:
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
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