Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Multiple fields from Multiple tables Resident Load

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?

15 Replies
sunny_talwar

How does that data look? Are there any common fields? Do you want to join them Row to Row?

Anonymous
Not applicable
Author

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

sunny_talwar

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

....

Anonymous
Not applicable
Author

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.

sunny_talwar

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.

Anonymous
Not applicable
Author

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?

sunny_talwar

You can take a resident of TempTable and RangeSum() all the Jan. But a better approach is to use ApplyMap here.

johnw
Champion III
Champion III

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
;

sunny_talwar

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