How to sum a field from fields in 3 load statements?
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?
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;
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,
Sum(AVAILABLE_CASH+ COMMITTED_CASH + OUTSTANDING_PRINCIPAL) as NewField