Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

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