Forum Discussion

WDW's avatar
WDW
New Member
5 years ago
Solved

Iterate through values to create a new column based on lookup from another table

I have a table named ChanData with 3 columns: timestamp, ChanID, Value. The data in the value column is the metric (si) value for the associated ChanID I have a second table named ConvFactor that has...
Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

    Let the data model do the work for you. What are your tables linked on?  Is the correction factor per channel ID stored somewhere?

     

    You can simplify your switch() statement like this:

     

    Imperial = SWITCH(

    [ChanID],106, [Value] * RELATED(ConvFactor[Conv])+(32),

    [Value] * RELATED(ConvFactor[Conv])

    )

     

lbendlin's avatar
lbendlin
Super User
5 years ago

Let the data model do the work for you. What are your tables linked on?  Is the correction factor per channel ID stored somewhere?

 

You can simplify your switch() statement like this:

 

Imperial = SWITCH(

[ChanID],106, [Value] * RELATED(ConvFactor[Conv])+(32),

[Value] * RELATED(ConvFactor[Conv])

)

 

WDW's avatar
WDW
New Member
5 years ago

  Thank you very much for your help, this mostly works for me. I apologize for the delayed reply. I have a lot to learn.  I left out an important detail in my original post -- approx 30% of my channels require the temperature adjustment which is identified in the Temp Add column of the ConvFactor table. I need to add an IF statement that looks for a 1 (True) to know to make the addition to the temperature convertion when required. This is what I have attempted:

Imperial =
SWITCH(
IF(RELATED(ConvFactor[Temp Add]) = 1,
[Value] * RELATED(ConvFactor[Conv])+(32),
[Value] * RELATED(ConvFactor[Conv]))

)
 
I get an error saying I do not have enough arguments for the SWITCH function

 

I was able to sort it out, I used the IF statement without the SWITCH and it is working as expected. Thank you for setting me on the right path.