13.2Linear Interpolation

I have 2 tables, shown below. Table 1 is (sort of) a reference/look up table, where it shows what eff% is at a specific P (MW). Tables 2 has the values that need to be looked up, it only has P, and I would like to determine, what the eff% should be (based on table 1).

I will note, that in some cases

Table 1:

Screenshot 2025-02-18 075340.png

Table 2:

Screenshot 2025-02-18 081951.png

Thank you!

Workflow

Prescriptive Analytics

Accepted answers

alexnajm

If you know how the Excel function calculates, then you may be able to find a more equivalent way in Alteryx. Otherwise I would go with this as the easiest Alteryx option

You can play with the underlying R code if you want, but only if you are comfortable in R

All comments

OTrieger

@HillaryLansfield
You can try using Join tool, joining on P

HillaryLansfield

I beleive the issue with that is the values in table 2, do not match up exactly with values in table 1. As a result, i believe i will need some sort of interpolation.

OTrieger

@HillaryLansfield
Try to round up the values to 1 decimal in both files

HillaryLansfield

I guess i provided a poor example. These are just mock numbers to illustrate the issue. There will be times when the values (even when rounded to a decimal) will not match. Ive updated the table to show this now.

OTrieger

If we are looking at rows 15 to 19, what will be the logic to depreciate them from each other?

HillaryLansfield

There isn't exactly logic. This represents a hydropower generator efficiency curve which is generated using real life testing. So based on our testing, if we operate between 1308 to 13.78MW, we will be at the same efficiency.

alexnajm

@HillaryLansfield, without logic as @OTrieger is asking for, it will be difficult to help further!

HillaryLansfield

Is there no way to interpolate using the data provided?

I am trying to replace an excel which I currently use to complete this task. When using excel, i use this:

=@LinearInterp(P (MW), look up table P (MW), look up table eff %)

this gives me the eff% with the associated P (MW) from table 2. Can this not be replicated in Alteryx?

alexnajm

There is, but you didn't give us this information until now and said "there isn't exactly logic" which is why we needed clarity. If you also provide what the result should be based on your current process, we may be able to replicate on our end. Workflows are also always preferred so we can test things out.

Otherwise my suggestion would be to either use the Linear Regression Tool since it'll likely be most similar to your function, or you can brute force it with Generate Rows to get all values in this range, Formula to calculate the corresponding value, and Join to match it up against your test set.

HillaryLansfield

I see, my mistake!

When using excel, here is the a few examples of input (P (MW)) and output (eff%)

input -> output

13.6 -> 93.3%

14 -> 92.5%

7-> 80.6%

Does that help?

Sorry for the confusion.

HillaryLansfield

I see, my mistake!

When using excel, here is the a few examples of input (P (MW)) and output (eff%)

input -> output

13.6 -> 93.3%

14 -> 92.5%

7-> 80.6%

Does that help?

Sorry for the confusion.

alexnajm

It does, but I don't want to type out the full table manually so here's a screenshot of what I'd try. Main table goes into Linear Regression as mentioned to predict Eff% as the target based on PMW, then feed the model into the Score tool along with the new data. Good luck!

Linear Interpolation.png

HillaryLansfield

Thank you! is there any any you would be able to attach your work flow? I am not sure exactly how to set up those tools (have never worked with them) and seeing your example may help!

alexnajm

@HillaryLansfield please try yourself first and come back with issues you run into - make sure you download the Predictive Tools as well: Download and Use Predictive Tools

HillaryLansfield

I gave it a try and believe I have it set up correctly ( i think at least). However, when i compare the the output form excel, the results are quite different. See workflow attached. Can you please let me know if you notice anything clearly incorrect?

Thanks!

Interpolation Trial.yxmd

alexnajm

It looks like your dataset is much smaller - make sure you have all data as this will influence the model output

Otherwise looks fine to me!

HillaryLansfield

thanks for pointing that out! I realize that I had pasted in the wrong data set.

With the correct data set, the output more closely matches that of excel. Is that any way to tweak the model so that they align better? Or is this unavoidable?

alexnajm

If you know how the Excel function calculates, then you may be able to find a more equivalent way in Alteryx. Otherwise I would go with this as the easiest Alteryx option

You can play with the underlying R code if you want, but only if you are comfortable in R

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703