Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
sbeaty
Contributor II
Contributor II

Adding new fields in data load editor

Hello. I am trying to add new fields to my table by using an aggregation.

Goal is to add 2 (up to 6) additional columns to give a higher overview of the customers as a whole for my table.
 
This is what I have below. The code runs, but when I look at my table the new field is there, but it only shows - in every row. Which I know is not true. Why isn't it summing the fields for me?? Thanks in advance!
 
 
CustomerTotals:
LOAD PLC_CustomerID
FROM
[lib://3_QVD_Generators\4_QVDs\InsuranceComm_Policy.qvd]
(qvd);
inner join (CustomerTotals)
load PM_CustomerID as PLC_CustomerID, sum(PLC_EarnedReserve) as TotalEarnedReserve
FROM
[lib://3_QVD_Generators\4_QVDs\InsuranceComm_PolicyMoney.qvd]
(qvd)
GROUP BY PM_CustomerID;
left join (CustomerTotals)
Load CL_CustomerID as PLC_CustomerID, sum(CL_SettlementAmount) as TotalLosses, Count(if(CL_SettlementAmount>0,CL_ClaimNumber)) AS TotalPaidCount, sum(CL_Reserve) as TotalClaimReserves
FROM
[lib://3_QVD_Generators\4_QVDs\InsuranceComm_Claims.qvd]
(qvd)
GROUP BY CL_CustomerID;
left join (CustomerTotals)
Load CLA_CustomerID as PLC_CustomerID, sum(CL_Salvage) as TotalSalvage
FROM
[lib://3_QVD_Generators\4_QVDs\InsuranceComm_ClaimAssets.qvd]
(qvd)
GROUP BY CLA_CustomerID;
Labels (4)
1 Solution

Accepted Solutions
rubenmarin

Hi, you can try to load it step by step and check wich join is giving you the empty rows, the you can check that data to see why the join is not working as expected. If you are sure that there is data for all the customers on the first load maybe it's a format issue: the codes on a qvd are stored as number and the other has them stored as string.

You can also try to load the join that fails as another table with another field names to compare values and check why some customers are not joining the data.

View solution in original post

2 Replies
rubenmarin

Hi, you can try to load it step by step and check wich join is giving you the empty rows, the you can check that data to see why the join is not working as expected. If you are sure that there is data for all the customers on the first load maybe it's a format issue: the codes on a qvd are stored as number and the other has them stored as string.

You can also try to load the join that fails as another table with another field names to compare values and check why some customers are not joining the data.

View solution in original post

sbeaty
Contributor II
Contributor II
Author

Thank you! The first join was giving me trouble.