Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

How to sum a field from fields in 3 load statements?

Hi,


I have 3 separate load statements, each with a field (marked in red) I would like to sum together to create a new field.

How does one build script to create this new field from each of three separate fields from different load statements.  Resident function seems to allow only reference to one not multiple other load statements?

Thanks,

AAA:
LOAD
      
ID as INVESTOR_AID,

        AVAILABLE_CASH;
FROM abc;

BBB:
LOAD
       
AID as INVESTOR_AID,
       
COMMITTED_CASH;
FROM def;
 

CCC:
LOAD
       
AID as INVESTOR_AID,
       
OUTSTANDING_PRINCIPAL;

FROM qwe;

1 Solution

Accepted Solutions

Re: How to sum a field from fields in 3 load statements?

if you have equal number of key in all the tables try like this :

AAA:
Mapping LOAD
      
ID as INVESTOR_AID,

        AVAILABLE_CASH;
FROM abc;

BBB:
Mapping LOAD
       
AID as INVESTOR_AID,
       
COMMITTED_CASH;
FROM def;

CCC:
LOAD
       
AID as INVESTOR_AID,
        applymap('AAA',AID)+applymap('BBB',AID)+
OUTSTANDING_PRINCIPAL;

FROM qwe;



OR


AAA:
LOAD
      
ID as INVESTOR_AID,

        AVAILABLE_CASH;
FROM abc;

join

BBB:
LOAD
       
AID as INVESTOR_AID,
       
COMMITTED_CASH;
FROM def;
join

CCC:
LOAD
       
AID as INVESTOR_AID,
       
OUTSTANDING_PRINCIPAL;

FROM qwe;



LOAD INVESTOR_AID,

AVAILABLE_CASH+COMMITTED_CASH+OUTSTANDING_PRINCIPAL as Total_Sum

resident

AAA;

View solution in original post

4 Replies

Re: How to sum a field from fields in 3 load statements?

if you have equal number of key in all the tables try like this :

AAA:
Mapping LOAD
      
ID as INVESTOR_AID,

        AVAILABLE_CASH;
FROM abc;

BBB:
Mapping LOAD
       
AID as INVESTOR_AID,
       
COMMITTED_CASH;
FROM def;

CCC:
LOAD
       
AID as INVESTOR_AID,
        applymap('AAA',AID)+applymap('BBB',AID)+
OUTSTANDING_PRINCIPAL;

FROM qwe;



OR


AAA:
LOAD
      
ID as INVESTOR_AID,

        AVAILABLE_CASH;
FROM abc;

join

BBB:
LOAD
       
AID as INVESTOR_AID,
       
COMMITTED_CASH;
FROM def;
join

CCC:
LOAD
       
AID as INVESTOR_AID,
       
OUTSTANDING_PRINCIPAL;

FROM qwe;



LOAD INVESTOR_AID,

AVAILABLE_CASH+COMMITTED_CASH+OUTSTANDING_PRINCIPAL as Total_Sum

resident

AAA;

View solution in original post

Highlighted

Re: How to sum a field from fields in 3 load statements?

AAA:
LOAD
      
ID as INVESTOR_AID,

        AVAILABLE_CASH as CASH,

       'ABC' a Flag
FROM abc;

BBB:
LOAD
       
AID as INVESTOR_AID,
       
COMMITTED_CASH as CASH,

       'DEF' a Flag
FROM def;
 

CCC:
LOAD
       
AID as INVESTOR_AID,
       
OUTSTANDING_PRINCIPAL as CASH,

        'QWE' a Flag

FROM qwe;



You can now use the Sum(CASH) in script as well as on Front end

Highlighted
MVP
MVP

Re: How to sum a field from fields in 3 load statements?

I would use Avinash's suggestion, bur change the addition to RangeSum, so that nulls don't affect your results:

RangeSum(applymap('AAA',AID), applymap('BBB',AID), OUTSTANDING_PRINCIPAL) as Total_Sum

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Highlighted
Not applicable

Re: How to sum a field from fields in 3 load statements?

Here is another solution that was sent to me by Krishnamoorthy Neeraj, Qlik Solution Architect:

Two ways to combine data from multiple tables to create a new field are :

1) Join 3 tables based on common keys such as investor aid, grade, term, state to form a combined table and then create a new field while doing a resident load from this combined table. However, while joining tables one should be mindful of one-many  or many-many relationships and avoid data duplication.

2) Another method that is used (works only when granularity of data is identical in all 3 tables ) is to concatenate 3 tables to form a resultant table . The new field is created with a combination of resident load and group by statement to create new field

E.g.  Load A,

                    B,

                    C,

                   Sum(AVAILABLE_CASH+ COMMITTED_CASH + OUTSTANDING_PRINCIPAL) as NewField

Resident CombinedTable

Group By A,B,C