Adding a row for a non existing transaction

Analytics
Enthusiast

Adding a row for a non existing transaction

 
7 REPLIES
Enthusiast

Re: Adding a row for a non existing transaction

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.

Enthusiast

Re: Adding a row for a non existing transaction

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. 

Enthusiast

Re: Adding a row for a non existing transaction

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.

Enthusiast

Re: Adding a row for a non existing transaction

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.

Junior Contributor

Re: Adding a row for a non existing transaction

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

Enthusiast

Re: Adding a row for a non existing transaction

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
Junior Contributor

Re: Adding a row for a non existing transaction

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.