Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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;
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
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
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