Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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
Highlighted

Re: Multiple fields from Multiple tables Resident Load

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

Highlighted
Contributor III
Contributor III

Re: Multiple fields from Multiple tables Resident Load

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

Highlighted

Re: Multiple fields from Multiple tables Resident Load

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

....

Highlighted
Contributor III
Contributor III

Re: Multiple fields from Multiple tables Resident Load

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.

Highlighted

Re: Multiple fields from Multiple tables Resident Load

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.

Highlighted
Contributor III
Contributor III

Re: Multiple fields from Multiple tables Resident Load

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?

Highlighted

Re: Multiple fields from Multiple tables Resident Load

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

Highlighted
MVP
MVP

Re: Multiple fields from Multiple tables Resident Load

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
;

Highlighted

Re: Multiple fields from Multiple tables Resident Load

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