Multiple fields from Multiple tables Resident Load

[Anonymous]

I have Four different Tables with different Fields as represented below:

Table1:

Jan1

Table2:

Jan2

Table3:

Jan3

Table4:

Jan4

I need to create a table Table 5 where I need to create a new field as Jan5 with Jan1+Jan2+Jan3+Jan4 as its value.

I thought about resident load but I can only pull one field from one table using resident load. How to I get to this?

[johnw]

I was hoping to avoid resident, but the problem I had was getting the complete list of UIDs into table 5 to apply the maps to. With only the mapping loads, when I tried to use fieldvalue() to efficiently get the complete list of UIDs, it just gave me an error, I assume because the mapping tables aren't considered REAL tables in the data model. That's why I had to resort to doing resident. It would be a much cleaner script if there were a way to avoid that.

Thoughts on doing it without the resident load?

If we COULD avoid the resident load, then I would indeed expect it to run faster with the mapping tables. But if the resident is necessary, I'm guessing it'd run slower. Testing would tell us for sure, but I'm not going to take the trouble, and testing results on random fake data might not be the same as testing results on the real data set.

I feel like I should advise people to keep the script simple even at the cost of efficiency. But I know our own publisher server is quite overloaded, so in practice, I end up doing anything I can do to be more efficient, even at the cost of complexity. I guess it depends on your environment. If you can get away with simple, I like simple.

[swuehl]

Another  option:

Table1:
LOAD * INLINE [
UID,Jan1
1,10
2,20
4,30
];
Table2:
LOAD * INLINE [
UID,Jan2
1,30
2,50
3,20
];

Table5:
LOAD *
,rangesum(lookup('Jan1','UID',UID,'Table1'),lookup('Jan2','UID',UID,'Table2')) as Jan5
;
LOAD num(fieldvalue('UID',recno())) as UID
AUTOGENERATE fieldvaluecount('UID')
;
DROP TABLES
Table1
,Table2
;

or

Table1:
LOAD * INLINE [
UID,Jan1
1,10
2,20
4,30
];

Concatenate
LOAD * INLINE [
UID,Jan2 
1,30
2,50
3,20
];


Table5:
LOAD UID, RangeSum(Only(Jan1),Only(Jan2)) as Jan5
Resident Table1
GROUP BY UID
;
DROP TABLES
Table1
;

  or

Table1:
LOAD UID, Jan1 as Jan INLINE [
UID,Jan1
1,10
2,20
4,30
];

LOAD UID, Jan2 as Jan INLINE [
UID,Jan2
1,30
2,50
3,20
];

Table5:
LOAD UID, Sum(Jan) as Jan5
Resident Table1
GROUP BY UID
;
DROP TABLES
Table1
;

[Anonymous]

I cannot pull data from one sheet to another using Resident Load , am I correct?

[swuehl]

I think I don't understand what you want to do, could you elaborate?

[Anonymous]

Let's say I have Table1 in Sheet1 and I want to use the fields in the Table1 in a different table on a different sheet. Resident load only works within the same sheet, am I right?

[swuehl]

Do you mean on a different tab of the script editor?

No, a resident LOAD is not limited to the same sheet/tab, the table just need to be part of the data model loaded so far.