All Forums Analytics
amerid 9 posts Joined 02/13
25 Dec 2014
Adding a row for a non existing transaction
amerid 9 posts Joined 02/13
25 Dec 2014

My client wants the count of items from store “A” aggregated by weekday/weekend sale, by grocery and brand.  I need also to add the expected count which is calculated by multiplying by a given multiplier (shown below)
W(North)=.43
W(South)=.57
E(North)=.49
E(South)=.51
 

YRMTH    Supplier  Store  WEEKDAYS/WEEENDS  Shipment  Brand    Sale   MNTHLY_TTL  EXP COUNT
201309   OWN       A      W                 North     B           1           13       5.59
201309   Other     A      W                 South     B          12           13       7.41
201309   OWN       A      E                 North     C           2            2       1.05

 
I cannot add expected value for Other Store A North as the store did not sell brand “C”. My database does not have any record because no sales occurred for that brand.
My question is …how can I add another row for the expected value as shown on the desired outcome below?
 

YRMTH    Supplier  Store  WEEKDAYS/WEEENDS  Shipment  Brand    Sale   MNTHLY_TTL  EXP COUNT
201309   OWN       A      W                 North     B           1           13       5.59
201309   Other     A      W                 South     B          12           13       7.41
201309   OWN       A      E                 North     C           0            2       1.03
201309   Other     A      E                 North     C           0            2       0.97

 
Any idea please? I  cannot show the real data but the answer for the above simplified example will help me figure how to modify my Teradata SQL code.
 
Thanks a lot.

Harpreet Singh 101 posts Joined 10/11
25 Dec 2014

Not clear on what you are trying to acehieve. Please answer that how is monthly total calculated as it is not clear from example. why is it 2 in last row for outcome instead of 0.
It seems that multiplier for Enorth and last two rows of final outcome are out of sync. 

amerid 9 posts Joined 02/13
26 Dec 2014

 

YRMTH    Supplier  Store  WEEKDAYS/WEEENDS  Shipment  Brand    Sale   MNTHLY_TTL  EXP COUNT
201309   OWN       A      W                 North     B           1           13       5.59
201309   Other     A      W                 South     B          12           13       7.41
201309   OWN       A      E                 North     C           2            2       1.05

This is what I can do with my sql code(The above result). The monthly count is a result of windows function which totals sales grouped by store,weekdays(W) for both north and south sale. This is all fine.
For the weekend(e) , I need to do the same but since there is no sale for E for South for brand C, the total remains to be 2. But my client needs another row that shows sales for South as 0 and the expected value as  0.97 (see below the desired outcome). My problem is since I dont have a zero sales for that store for that brand , how can I create(add) a row that reflects a zero sales and .97 expected count?

YRMTH    Supplier  Store  WEEKDAYS/WEEENDS  Shipment  Brand    Sale   MNTHLY_TTL  EXP COUNT
201309   OWN       A      W                 North     B           1           13       5.59
201309   Other     A      W                 South     B          12           13       7.41
201309   OWN       A      W                 North     C           0            2       1.03
201309   Other     A      W                 North     C           0            2       0.97

 
Hope this is clear. Thanks a lot.
 
 

amerid 9 posts Joined 02/13
27 Dec 2014

Correction to the above...please ignore the second table above
 
the desired table looks like below
 

YRMTH    Supplier  Store  WEEKDAYS/WEEENDS  Shipment  Brand    Sale   MNTHLY_TTL  EXP COUNT
201309   OWN       A      W                 North     B           1           13       5.59
201309   Other     A      W                 South     B          12           13       7.41
201309   OWN       A      E                 North     C           2            2       1.03
201309   Other     A      E                 South     C           0            2       0.97

 
My problem is calculating expecting count for South C . If my total shows 0 in my table it would have been easy. My Windows count would have captured it but since I do not have any sales, there is no row for that sale.
Hope it is clear now. Thanks.
 

dnoeth 4628 posts Joined 11/04
02 Jan 2015

The only way to get non-existing data is using a list of all needed values, e.g. in a Left Join

Dieter

amerid 9 posts Joined 02/13
02 Jan 2015

Hello Dieter,
As you may see in my simplified SQL below, I am left joining but the problem is that there is no record for sales if sales does not happen for a particular brand in that month. Sales is not regitered as 0  if there is no sale. But I am expected to enter expected valur of .97 buy multipling the store's multiplier for the monthly total which is 2.
My question is how can I display an expected value where I  do not have any reord in my left table(sales)?
Thank you for your help.
 

SEL DISTINCT 

D.YRMTH,
D.SUPPLIER,
D.STORE,
D.WE_WD  AS "WEEKDAYS/WEEKENDS"
D.SHIPMENT,
D.BRAND,
SUM(SALE),
SUM(SALE)OVER(PARTITION BY D.YRMTH,D.STORE,D.WE_WD,D.BRAND,D.SUPPLIER,D.SHIPMENT  ORDER BY D.YEARMTH,D.STORE,D.WE_WD,D.BRAND) MNTHLY_TTL  ,                                 
ZEROIFNULL(Q.MULTIPLIER)*ZEROIFNULL(MNTHLY_TTL)   Exp_Cnt,
FROM SALES D
LEFT OUTER  JOIN  SALES_MULTIPLIER Q
ON D.YRMTH Q.YRMTH
 AND D.STORE=Q.STORE AND  D.WE_WD=Q.WE_WD
AND D.BRAND=Q.BRAND

WHERE 

AND D.YRMTH= '201309'
AND D.STORE='A'
AND D.YRMTH='201309'
GROUP BY  1,2,3,4,5,6,9

 

dnoeth 4628 posts Joined 11/04
02 Jan 2015

You might already have all needed rows in the SALES_MULTIPLIER table.
Of course then you need to change the join to a RIGHT join and change the columns from D.whatever to Q.whatever in your SELECT/WHERE-list.
And I don't think your calculation of MNTHLY_TTL is valid, this should be SUM(SUM(SALE)) OVER and there's no need to ORDER.

Dieter

You must sign in to leave a comment.