Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
How does that data look? Are there any common fields? Do you want to join them Row to Row?
Hi Sunny
Table1 Table2 Table3 Table4
-------- --------- -------- --------
Jan1 Jan2 Jan3 Jan4
All the Jan1, Jan2, Jan3, Jan4 fields have numbers as values. Clearly, there are no common fields. I can add a common field if that helps me in creating this new table as below:
Table5
--------
Jan5
The resultant Jan5 Field should have the values that are created from Jan1 + Jan2 + Jan3 + Jan4
Lets say this is the data
Jan 1
10
12
32
Jan 2
32
12
14
and so on...
How would you join them?
Jan 1 + Jan 2
10 + 12
12 + 12
32 + 14
....
To make things easier, I can add another Field called UID to every table.
So this would be the case
Table1 Table2
------- ------------
UID Jan1 UID Jan2
--- ------- ----- -------
1 10 1 30
2 20 2 50
4 30 3 20
........
So the values should be added based on their UID match. That should work.
Exactly so you can create something like this:
TempTable:
LOAD Jan1,
RowNo() as Key //or RecNo() one of them should work
Resident Table1;
Left Join (TempTable)
LOAD Jan2,
RowNo() as Key
Resident Table2;
and son on
Or you can use a Mapping Load which is probably would be a better option.
I see that this will only join the tables. But I need the new table (Table5) with a new field Jan5 as sum of all the Jan1, Jan2...fields.
Am I understanding right?
You can take a resident of TempTable and RangeSum() all the Jan. But a better approach is to use ApplyMap here.
Not sure if I understood your intent, Sunny T, but this is all I could come up with quickly for a mapping load version:
Table1:
LOAD * INLINE [
UID,Jan1
1,10
2,20
4,30
];
Table2:
LOAD * INLINE [
UID,Jan2
1,30
2,50
3,20
];
Map1: MAPPING LOAD * RESIDENT Table1;
Map2: MAPPING LOAD * RESIDENT Table2;
Table5:
LOAD *
,rangesum(applymap('Map1',UID,0),applymap('Map2',UID,0)) as Jan5
;
LOAD num(fieldvalue('UID',recno())) as UID
AUTOGENERATE fieldvaluecount('UID')
;
DROP TABLES
Table1
,Table2
;
And that's longer and less clear than just joins:
Table5:
LOAD * INLINE [
UID,Jan1
1,10
2,20
4,30
];
OUTER JOIN (Table5)
LOAD * INLINE [
UID,Jan2
1,30
2,50
3,20
];
LEFT JOIN (Table5)
LOAD UID
,rangesum(Jan1,Jan2) as Jan5
RESIDENT Table5
;
DROP FIELDS
Jan1
,Jan2
;
John Witherspoon I feel that we won't need to do a resident to calculate Jan5 if we use Mapping Load. Won't that be little more better. In addition joins are usually considered to be slower then Mapping Loads. May be we have extra script, but if it is efficient, then why not use it?
I may be wrong, and would look to you for guiding me