Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
farnsworth
Contributor III
Contributor III

Script Joining Sum Error

Hi all,

I have the following script running and I get a strange error which I cannot eliminate.  The total in the raw data is 10,842 by running with the Report Date the total is 10,842.  If it run the total by month not including Report Date I get 10,842.  Yet this join means the daily total is 10,842 but the Monthly Total comes out as 10,843 can anyone see what is wrong in this?

NoConcatenate

DAILY:

LOAD

  MonthYear

  ,[Report Date]

  ,Product

  ,Channel

  ,MonthAge

  ,SUM(Quotes) AS Quotes

  ,SUM(Sales) AS Sales

  ,SUM(Day0Sales) AS Day0Sales

RESIDENT

  QUOTESANDSALES

GROUP BY

  MonthYear

  ,[Report Date]

  ,Product

  ,Channel

  ,MonthAge

;

//Calculate the total quotes and sales volumes by month

LEFT JOIN (DAILY)

LOAD

  MonthYear

  ,Product

  ,Channel

  ,MonthAge

  ,SUM(Quotes) AS MonthlyQuotes

  ,SUM(Sales) AS MonthlySales

  ,SUM(Day0Sales) AS MonthlyDay0Sales

RESIDENT

  QUOTESANDSALES

GROUP BY

  MonthYear

  ,Product

  ,Channel

  ,MonthAge

;

DROP TABLE QUOTESANDSALES;

3 Replies
micheledenardi
Specialist II
Specialist II

Can you upload some example data ?

Michele De Nardi
If a post helps to resolve your issue, please accept it as a Solution.
lironbaram
Partner - Master III
Partner - Master III

hi

if you do not join the tables

is the Monthly total ok

drop the left join from the script

and if it's ok , then you have duplication in the left join

farnsworth
Contributor III
Contributor III
Author

So the join didn't work.  Basically what I want to get to is as below

  

Report DateDailyTotal%
01/05/2017642108435.9%
02/05/2017687108436.3%
03/05/2017651108436.0%
04/05/2017590108435.4%
05/05/2017606108435.6%
06/05/2017564108435.2%
07/05/2017512108434.7%
08/05/2017670108436.2%
09/05/2017587108435.4%
10/05/2017554108435.1%
11/05/2017541108435.0%
12/05/2017629108435.8%
13/05/2017593108435.5%
14/05/2017472108434.4%
15/05/2017734108436.8%
16/05/2017656108436.0%
17/05/2017608108435.6%
18/05/2017546108435.0%

Only problem is the total is 10,842  So the calculation I want is Daily / Sum(Daily)