Joining through a select statement is causing me to lose data that appears duplicate

[Anonymous]

I am using the script below to join several tables together to come up with my total Sub Trade Cost: It appears to work fine until there are duplicate cost within the table under 1 job ID .

Example:

JobID	JobDeF	Amount
1	Sub Trade	$100
1	Sub Trade	$200
1	Sub Trade	$50
1	Sub Trade	$100
1	Sub Trade	$75
2	Expenses	$25
2	Sub Trade	$50
2	Sub Trade	$100

What I want is in the visualization table the Sum(Sub_Trade_Amount) to show $525 for JobID #1 but what I am getting is $425 because the $100 shows up twice. Where JobID #2 gives me $150 which is correct. It took me a little while to figure out why my numbers would match for some JobID's but not all and this is what I found as a result. Please help me figure out how to get this to calculate all totals. Thank  you for your help.

//****Sub Trade Amount Query ****

SubTrade_Amount:

Load *

Where ((JobID) AND ((SubTrade_JobDeF) Like '*Sub-Trades*'));

Load

JobID,

JobDeF as SubTrade_JobDeF,

Amount as SubTrade_Amount;

SELECT

tbl_JCJobs.JobID, tbl_JCExpensesReal.JobDeF, tbl_JCExpensesReal.Amount

FROM ((tbl_JCJobs INNER JOIN tbl_JCPhaseIdEst ON tbl_JCJobs.JobID = tbl_JCPhaseIdEst.JobID)

INNER JOIN tbl_JCSubphaseEst ON tbl_JCPhaseIdEst.Phaseid = tbl_JCSubphaseEst.Phaseid)

INNER JOIN tbl_JCExpensesReal ON tbl_JCSubphaseEst.SubphaseId = tbl_JCExpensesReal.SubphaseId

GROUP BY tbl_JCJobs.JobID, tbl_JCExpensesReal.JobDeF, tbl_JCExpensesReal.Amount;

//Having (((tbl_JCJobs.JobID)=[JOBID]) AND ((tbl_JCExpensesReal.JobDeF) Like "*sub*"));

[Anil_Babu_Samineni]

Make sense !!! Here, Will you able to provide screen shot for that because i am getting $525 only. I used below one. Aren't you?

Sum({<SubTrade_JobDeF = {'Sub Trade'}>}SubTrade_Amount)

[Anonymous]

Where are you using that Formula? I tried replacing my Where clause with it but it doesn't work as my data wont load. Am I correct in using a Where clause with a preceding load since Qlik Sense does not allow Having Statements?

[Anonymous]

[Anonymous]

I attached 2 screen shots, 1 of the program who's data base I am pulling my tables from and the other from my sheet in Qlik Sense. I am having the same issue with the Equipment, and Expenses tables in my App Sheet as I am with Sub-Trade. It took me awhile to figure out that the issue is that it is not adding numbers that are the same. If you look at the screen shot of my data base program, you will see the table has 2 values of $2340.00 and 2 values of $1139.67. The program is correctly adding them but Qlik Sense is not. I am using the Same query as the the program which is a SQL Server. The only difference in the Queries is that Qlik does not use having statements so I used a where clause with a preceding load. I am knew at Qlik so I am not sure if this is what is causing the issues.