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

[#1]

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;

[avinashelite · Accepted solution]

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;

[Kushal_Chawda]

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

[jonathandienst]

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

[#5]

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