Moving sales to different dates

[hobanwashburne]

I need to re-create a report that moves any sales made during a non-working day to the following working day.

For example assuming a non-working weekend:

Actual Data

Day	Sales
Fri	12,531
Sat	852
Sun	612
Mon	18,187
Tues	16,873

Desired output

Day	Sales
Fri	12,531
Sat	0
Sun	0
Mon	19,651
Tues	16,873

[aarkay29]

Try this:

Orders:

LOAD

  Cust,

     Net,

     Date

FROM

[Data 2.xlsx]

(ooxml, embedded labels, table is Orders);

Left Join (Orders)

NonWorkingDays:

LOAD

  Date,

     HolidayFlag

FROM

[Data 2.xlsx]

(ooxml, embedded labels, table is NonWorkingDays);

Load

     Cust,

     Net,

     Date,

     If(peek(HolidayFlag)=1,Net+peek(NetSales),Net) as NetSales

Resident

     Orders;

Drop table

     Orders;

[hobanwashburne]

That didn't seem to work for me

[tunoi · Accepted solution]

This works?

Orders:

LOAD

  Cust,

     Net,

     Date

FROM

[Data 2.xlsx]

(ooxml, embedded labels, table is Orders);

left join(Orders)

LOAD

  Date,

     HolidayFlag

FROM

[Data 2.xlsx]

(ooxml, embedded labels, table is NonWorkingDays);

temp_calendar:

Load Distinct

  Date,

  HolidayFlag

resident

  Orders

;

New_Date:

Load

  1 as Date,

  1 as New_Date

autogenerate(0)

;

for i = 0 to NoOfRows('temp_calendar') - 1

  let v_date = num(peek('Date', $(i), 'temp_calendar'));

  let v_holiday = alt(peek('HolidayFlag', $(i), 'temp_calendar'),0);

  if $(v_holiday) = 0 then

  concatenate (New_Date)

  load

  date($(v_date)) as Date,

  date($(v_date)) as New_Date

  autogenerate(1);

  else

  concatenate (New_Date)

  load

  date($(v_date)) as Date,

  date(min(Date)) as New_Date

  Resident

  temp_calendar

  where

  Date > $(v_date) and HolidayFlag <> 1;

  end if;

Next i;

drop table temp_calendar;

Left join(Orders)

load

  Date,

  New_Date

resident

  New_Date

;

drop table New_Date ;

[aarkay29]

Alternate solution

PFA