topic Re: Multiple fields from Multiple tables Resident Load in QlikView

Multiple fields from Multiple tables Resident Load

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Multiple fields from Multiple tables Resident Load

sunny_talwar — Thu, 21 Apr 2016 20:40:36 GMT

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

Re: Multiple fields from Multiple tables Resident Load

Anonymous — Thu, 21 Apr 2016 20:44:57 GMT

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

Re: Multiple fields from Multiple tables Resident Load

sunny_talwar — Thu, 21 Apr 2016 20:49:00 GMT

Lets say this is the data

Jan 1

Jan 2

and so on...

How would you join them?

Jan 1 + Jan 2

10 + 12

12 + 12

32 + 14

....

Re: Multiple fields from Multiple tables Resident Load

Anonymous — Thu, 21 Apr 2016 20:53:37 GMT

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.

Re: Multiple fields from Multiple tables Resident Load

sunny_talwar — Thu, 21 Apr 2016 20:56:19 GMT

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.

Re: Multiple fields from Multiple tables Resident Load

Anonymous — Thu, 21 Apr 2016 21:01:43 GMT

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?

Re: Multiple fields from Multiple tables Resident Load

sunny_talwar — Thu, 21 Apr 2016 22:05:00 GMT

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

Re: Multiple fields from Multiple tables Resident Load

johnw — Thu, 21 Apr 2016 22:05:52 GMT

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
;

Re: Multiple fields from Multiple tables Resident Load

sunny_talwar — Thu, 21 Apr 2016 22:10:03 GMT

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

Re: Multiple fields from Multiple tables Resident Load

johnw — Thu, 21 Apr 2016 22:22:29 GMT

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.

Re: Multiple fields from Multiple tables Resident Load

swuehl — Thu, 21 Apr 2016 23:26:45 GMT

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
;

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
;

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
;

Re: Multiple fields from Multiple tables Resident Load

Anonymous — Fri, 22 Apr 2016 13:49:53 GMT

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

Re: Multiple fields from Multiple tables Resident Load

swuehl — Fri, 22 Apr 2016 13:55:44 GMT

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

Re: Multiple fields from Multiple tables Resident Load

Anonymous — Fri, 22 Apr 2016 14:01:17 GMT

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?

Re: Multiple fields from Multiple tables Resident Load

swuehl — Fri, 22 Apr 2016 14:08:11 GMT

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.