topic Add value to field via load script in App Development

Add value to field via load script

Moysie — Mon, 03 Feb 2025 14:04:35 GMT

Hi,

I have a 3 tables, the first is a list of users, the second is a resourcing table listing the dates, by day that the users are resourced against a project. The last table is a table if dates by day running from 2016 - 2030. Some of the users are not resource at all, so then I look at the pivot table results the user does not show and I have a lot of null values.

Table 1 Users

Users

Table 2

Resource_User

Resource_Date

table 3

Resource_Date

What I would like to know is if it is possible to check if the User is resourced as Resourced_User for the Resource_Date and if they are not inset a 0 into the line to table 2.

Thanks in advance.

Re: Add value to field via load script

marcus_sommer — Mon, 03 Feb 2025 15:45:24 GMT

You may apply a join approach like:

t: load distinct User from T1;
join(t) load distinct Date from T3;
left join(t) load User, Date, F1, F2, ... from T2;

whereby the first join creates a cartesian product and the second join adds the real existing ones to it.

Another approach may include the use of where-clauses with (not) exists() and depending on the load-order and data-set with one or two parameters - which is quite clear for me with the given example and description. Exists() is especially then suitable if only missing items should be detected/added and not more or less continuous ranges.