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

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

Wed, 11 Nov 2015 06:29:13 GMT

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;

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

avinashelite — Wed, 11 Nov 2015 06:38:35 GMT

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;

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;

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

Kushal_Chawda — Wed, 11 Nov 2015 06:40:37 GMT

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

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

jonathandienst — Wed, 11 Nov 2015 07:56:40 GMT

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

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

Sat, 14 Nov 2015 18:10:07 GMT

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,

Sum(AVAILABLE_CASH+ COMMITTED_CASH + OUTSTANDING_PRINCIPAL) as NewField

Resident CombinedTable

Group By A,B,C