Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
hobanwashburne
Creator
Creator

Moving sales to different dates

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

DaySales
Fri12,531
Sat852
Sun612
Mon18,187
Tues16,873

Desired output

DaySales
Fri12,531
Sat0
Sun0
Mon19,651
Tues

16,873

1 Solution

Accepted Solutions
tunoi
Creator
Creator

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 ;

View solution in original post

4 Replies
aarkay29
Specialist
Specialist

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
Creator
Creator
Author

That didn't seem to work for me

tunoi
Creator
Creator

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
Specialist
Specialist

Alternate solution

PFA