Set up a scoring system based on two variables

[mikegrattan]

I'd like to set up scores based on two variables, Temperature and Humidity.  For example:

If Temp = 15c and Humidity = 80%, then the score is 3.4

If Temp = 23c and Humidity = 70%, the score is 8.5

The scores themselves are not calculated, they will be manually entered either into an input table, an inline load, or in an equation as a variable in the application.  At this point I've looked at IntervalMatch but I don't think it can do this, so I'm looking for ideas and suggestions.  Should I just load this data into a table and join it to my main data by the Temp and Humidity fields?

Example Data:

[Kushal_Chawda]

@mikegrattan  If you have score data with temp and humidity  it is better to join that data with main data set having the same fields. If your question is about loading data into table then there are many ways you can do it. If your score data is in spreadsheet or text file then you an directly load and join it with your actual dataset. If your score data is in database, then you can create qvd by loading it from database and then you can join that qvd with actual dataset.

[mikegrattan]

@Kushal_Chawda  The data for scoring currently only exists in an Excel file but it is arranged like the screen shot I posted.  The challenge for me is trying to figure out how to arrange this data in a way that can be joined to the data in my application.  My application currently has fields for temp and humidity but I can't think of a way to add a score by joining this scoring data from Excel...at least, not the way it's currently arranged.

[Kushal_Chawda · Accepted solution]

@mikegrattan  you can use crosstable load to make your data in usable format.

https://help.qlik.com/en-US/sense/February2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptPref...

score_data:
crosstable (Percent,Score,2)
LOAD [C],
     [F],
      *
FROM table;

Now you can join the above table with actual table or any other dataset with resident load

// rename your field to match with existing dataset fieldname
left join(YourTable)
LOAD [C],
     [F],
     Percent,
     Score
resident score_data;

drop table score_data;

[mikegrattan]

@Kushal_Chawda 

I will try the crosstable load.  I do have a question about it though; it looks like the score wouldn't be matched with values in my existing data if those values are not exact matches.  Our data includes temperatures like 15.6c which should probably be rounded up to 16c for the purposes of scoring, as well as humidity percents that will be between the values in the scoring table.  So the logic for scoring if you look at the first two rows in the table would be something like this:

If the temp in centigrade is between 15 and 15.9 and the relative humidity percent is between 70 and 74.9, then the score is 5.1.  If the temp is between 15 and 15.9 and the humidity is between 75 and 79.9, then the score is 4.2.

I'm guessing that I would have to expand this table and include all incremental values for temperature and humidity?