I wand to change a date from Saturday and Sunday to Monday

[#1]

What i trying to do is change a date being loaded if it is a Sat or Sun date i would like to change date to Monday

simple example   load script:

LOAD

  Material,

  Request_date,

  qtty

FROM.......

I would like to create Newdate field that would move any sat or Sun date to Monday.

the  " dow " column is just informational so you can see days of week

Requestdate	Newdate	Qty	dow
7/17/2015	7/17/2015	1	Friday
7/17/2015	7/17/2015	1	Friday
7/18/2015	7/20/2015	1	Saturday
7/18/2015	7/20/2015	1	Saturday
7/18/2015	7/20/2015	1	Saturday
7/18/2015	7/20/2015	1	Saturday
7/19/2015	7/20/2015	1	Sunday
7/20/2015	7/20/2015	1	Monday
7/21/2015	7/21/2015	1	Tuesday
7/22/2015	7/22/2015	1	Wednesday
7/23/2015	7/23/2015	1	Thursday
7/24/2015	7/24/2015	1	Friday
7/25/2015	7/27/2015	1	Saturday
7/26/2015	7/27/2015	1	Sunday
7/27/2015	7/27/2015	1	Monday
7/28/2015	7/28/2015	1	Tuesday
7/29/2015	7/29/2015	1	Wednesday

what i am then trying to create from this data would be a Stdev by Material by date( total) .

note: 7/20 newdate total is 6.

Requestdate	dow	Total
7/17/2015	Friday	5
7/20/2015	Monday	6
7/21/2015	Tuesday	1
7/22/2015	Wednesday	1
7/23/2015	Thursday	1
7/24/2015	Friday	1
7/27/2015	Monday	3
7/28/2015	Tuesday	1
7/29/2015	Wednesday	1
	standard deviation for this item is	1.986062548

thanks for the help Scott

[swuehl]

Try something like

LOAD

     LastWorkDate(Requestdate, 1) as Newdate,

     Requestdate,

...

[maxgro]

LOAD

  Requestdate,

   pick(wildmatch(num(WeekDay(Requestdate)), 5, 6, '*'), date(Requestdate+2), date(Requestdate+1), Requestdate)           as NewDate, 

    Qty, dow, WeekDay(Requestdate)

FROM

[https://community.qlik.com/thread/173112]

(html, codepage is 1252, embedded labels, table is @1);

[sasiparupudi1]

DayNamesMap:

Mapping LOAD * Inline

[

ShortName,LongName

0,Monday

1,Tuesday

2,Wednesday,

3,Thursday

4,Friday

5,saturday

6,Sunday

];

Data:

Load Requestdate,Qty,Newdate,ApplyMap('DayNamesMap',num(WeekDay(Newdate)),'NA')as dow;

load Requestdate,Qty,

if(Num(WeekDay(Date#(Requestdate,'MM/DD/YYY')))=5,Date(Date#(Requestdate,'MM/DD/YYY')+2,'M/DD/YYY') ,if(Num(WeekDay(Date#(Requestdate,'MM/DD/YYY')))=6,Date(Date#(Requestdate,'M/DD/YYY')+1,'MM/DD/YYY'),Date(Date#(Requestdate,'M/DD/YYY'),'M/DD/YYY')))  as Newdate

Inline

[

Requestdate,Qty

7/17/2015,1

7/17/2015,1

7/18/2015,1

7/18/2015,1

7/18/2015,1

7/18/2015,1

7/19/2015,1

7/20/2015,1

7/21/2015,1

7/22/2015,1

7/23/2015,1

7/24/2015,1

7/25/2015,1

7/26/2015,1

7/27/2015,1

7/28/2015,1

7/29/2015,1

];

HTH

Sasi

[#5]

Thank you Massimo Grossi  this worked for me.