Previous row in script

[#1]

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

[sunny_talwar]

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

[#3]

Yes..if the product is having more than 1 entry for single date.then it should be the sum.

Thanks,

Reena

[sunny_talwar]

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;

[#5]

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

[effinty2112]

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;

[sunny_talwar]

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;