I have previously loaded a few tables with a few variables. Now I want to create a new table with some additional variables:
Calculations:
load if(Number_number>=Bed_min and Bed_number<Bed_max and len(trim(Problem_in_room))=0 and not len(trim(TTimeStamp))=0 ,1,0) as Calc_StateOK,
TTimeStamp;
This does not produce the desired table, which is a table containing the calculated Boolean value and the TTimeStamp What's wrong? Do you have any ideas?
You can't just call the field values from different tables like that. The functions will be evaluated record based using the input table stated by resident (or from ...) statement.
It is depending on what you want to achieve, there are probably some methods to achieve what you want.
For example, you can use lookup() function to access another table/field using a search field (could be Room_id).
Or you could join your two tables, then you have all fields in one input table and you can use the load script you posted above.
If you could post your complete script or best a small sample app (with attached excel to load or INLINE data, so a reload is possible), I think someone here might help you pretty fast.
I could equally well do this in the expression for my charts, but in that case, the loading of the chart takes like 5 minutes, hence it would be easier to safe the expression as a field.
it's still a bit unclear to me how your data is related. Your Problem_Table only has Room_ID as link, but could show multiple Problems per Room_ID. In your Table_Data, you could have multiple entries for Room_ID, too, with different or same Bed_Number and Date. Your Calc_StateOK is checking some conditions, but for some TTimeStamps, there can be multiple, different results.
If you could do your evaluation in the front end, it would be good if you could add a chart table with your expression to the sample.
Thank you for your very important remark. This is actually a crucial issue! But we have already found a solution for that. 🙂 - Hence, let's just focus on the question, how I can use values from different tables in an IF statement upon load.
