Get date from day of week

[#1]

Hi all,

I have dates from 17/12/2015 to 23/12/2015 (in the format DD/MM/YYYY).

Also using the function WeekStart() and WeekEnd(), I can find the start and end of the week for the particular dates.

My requirement is I have a field with the name of the day (Sun,Mon,Tue,.... , Sat).

If for the particular date name of day says Tue, the value should be the date between WeekStart and Weekend which is Tuesday.

Example:

Date	WeekStart (Monday)	WeekEnd (Sunday)	Value	New_Date
17/12/2015	14/12/2015	20/12/2015	Tue	15/12/2015
18/12/2015	14/12/2015	20/12/2015	Fri	18/12/2015
19/12/2015	14/12/2015	20/12/2015	Fri	18/12/2015
20/12/2015	14/12/2015	20/12/2015	Wed	16/12/2015
21/12/2015	21/12/2015	27/12/2015	Sat	26/12/2015
22/12/2015	21/12/2015	27/12/2015	Mon	21/12/2015
23/12/2015	21/12/2015	27/12/2015	Fri	25/12/2015

Can anyone please help me on this?

Regards,

Anjali Gupta

[Anonymous]

You want to do it on the base of New_Date Field??

Could you elaborate more?

[sunny_talwar · Accepted solution]

Try this script:

Table:

LOAD Date,

     [WeekStart (Monday)],

     [WeekEnd (Sunday)],

     Value,

     New_Date,

     Date(([WeekStart (Monday)] + Pick(Match(Value, 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun'), 0, 1, 2, 3, 4, 5, 6))) as Calculated_New_Date

FROM

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

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

[MayilVahanan]

Hi

We can achieve this in different ways. Here, 2 methods

MapWeek:

Mapping

LOAD * Inline

[

Value, Num

Mon, 1

Tue, 2

Wed, 3

Thu, 4

Fri, 5

Sat, 6

Sun, 7

];

LOAD *, Date(Date+((Week - WeekValue)*-1)) as FinalDate,

               Date("WeekStart (Monday)" + (WeekValue-1)) as New_Date;

LOAD *, ApplyMap('MapWeek', Text(WeekDay(Date))) as Week, ApplyMap('MapWeek', Value) as WeekValue INLINE [

    Date, WeekStart (Monday), WeekEnd (Sunday), Value

    17/12/2015, 14/12/2015, 20/12/2015, Tue

    18/12/2015, 14/12/2015, 20/12/2015, Fri

    19/12/2015, 14/12/2015, 20/12/2015, Fri

    20/12/2015, 14/12/2015, 20/12/2015, Wed

    21/12/2015, 21/12/2015, 27/12/2015, Sat

    22/12/2015, 21/12/2015, 27/12/2015, Mon

    23/12/2015, 21/12/2015, 27/12/2015, Fri

];

O/P:

Date	New_Date	FinalDate	Week	WeekEnd (Sunday)	WeekStart (Monday)	Value	WeekValue
17/12/2015	15/12/2015	15/12/2015	4	20/12/2015	14/12/2015	Tue	2
18/12/2015	18/12/2015	18/12/2015	5	20/12/2015	14/12/2015	Fri	5
19/12/2015	18/12/2015	18/12/2015	6	20/12/2015	14/12/2015	Fri	5
20/12/2015	16/12/2015	16/12/2015	7	20/12/2015	14/12/2015	Wed	3
21/12/2015	26/12/2015	26/12/2015	1	27/12/2015	21/12/2015	Sat	6
22/12/2015	21/12/2015	21/12/2015	2	27/12/2015	21/12/2015	Mon	1
23/12/2015	25/12/2015	25/12/2015	3	27/12/2015	21/12/2015	Fri	5

[#5]

Thanks Sunny for the quick response and help.

It worked out perfectly.

Regards,

Anjali Gupta

[#6]

Thanks for your help also.

[sunny_talwar]

Awesome, I am glad it helped you