Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need have date ,product,region and district as fields in my script.
Now i need to get the previous volume for each date based on product.How can i achieve that??
Kindly help.
Regards,
Reena
This make sense, but what if you have multiple value for a product on a single day, may be like this:
Date Product Volume Output
01-01-15 ABC 100 0
01-01-15 BCA 200 0
01-01-15 ACB 400 0
01-01-15 ABC 100 0
02-01-15 ABC 250 100 -> Would this now be 200? since we have two entries on previous date?
02-01-15 BCA 50 200
02-01-15 ACB 150 400
02-01-15 HIJ 150 0
Yes..if the product is having more than 1 entry for single date.then it should be the sum.
Thanks,
Reena
This?
Script:
Table:
LOAD Date(DayId, 'YYYYMMDD') as DayId,
Date(Date#(WeekEndingDate, 'MM/DD/YYYY')) as WeekEndingDate,
DistrictIdName,
RegionIdName,
Product,
Volume
FROM
TestDATAVOLume.xls
(biff, embedded labels, table is Sheet2$);
Temp:
LOAD DayId,
WeekEndingDate,
Product,
Sum(Volume) as SumOfVolume
Resident Table
Group By DayId, WeekEndingDate, Product;
FinalTable:
LOAD *,
If(Product = Previous(Product), Previous(SumOfVolume), 0) as PreviousSumOfVolume
Resident Temp
Order By Product, WeekEndingDate;
DROP Table Temp;
Hi Sunny,
Thanks a lot for the reply.
it works for Product.
but if i need to consider both region and District what changes has to be done?
Regards,
Reena
Hi Reena
This script gives this output, the volumes are aggregated by district, region, product and day:
Data:
LOAD DayId,
DistrictIdName,
RegionIdName,
Product,
Sum(Volume) as Volume
FROM
[..\Community\Previous row in script\TestDATAVOLume.xls] //change this path
(biff, embedded labels, table is Sheet2$)
Group by
DistrictIdName,
RegionIdName,
Product,
DayId;
NoConcatenate
DataWithPrevDay:
LOAD
if( DistrictIdName & '|' & RegionIdName & '|' & Product =
Previous(DistrictIdName) & '|' & Previous(RegionIdName) & '|' & Previous(Product),
Date(Previous(DayId)), '') as PreviousDayId,
if( DistrictIdName & '|' & RegionIdName & '|' & Product =
Previous(DistrictIdName) & '|' & Previous(RegionIdName) & '|' & Previous(Product),
Previous(Volume)) as PreviousVolume,
DayId,
DistrictIdName,
RegionIdName,
Product,
Volume
Resident Data Order by DistrictIdName,
RegionIdName,
Product, DayId;
drop Table Data;
Try this:
Table:
LOAD Date(DayId, 'YYYYMMDD') as DayId,
Date(Date#(WeekEndingDate, 'MM/DD/YYYY')) as WeekEndingDate,
DistrictIdName,
RegionIdName,
Product,
Volume
FROM
TestDATAVOLume.xls
(biff, embedded labels, table is Sheet2$);
Temp:
LOAD DayId,
WeekEndingDate,
RegionIdName,
DistrictIdName,
Product,
Product & '|' & RegionIdName & '|' & DistrictIdName as Key,
Sum(Volume) as SumOfVolume
Resident Table
Group By DayId, WeekEndingDate, Product, RegionIdName, DistrictIdName;
FinalTable:
LOAD *,
If(Key = Previous(Key), Previous(SumOfVolume), 0) as PreviousSumOfVolume
Resident Temp
Order By Key, WeekEndingDate;
DROP Tables Temp, Table;