Custom weeks

[viveksingh]

Hi experts,
I have data like below
Week_st Week_end week
01/10/2018 05/10/2018 Week1
06/10/2018 12/10/2018 Week2
13/10/2018 25/10/2018 Week3

I want to creat week nunbers like week1,2,3,... based on above data

Expecting like below
Week_st Week_end week
01/10/2018 05/10/2018 Week1
06/10/2018 12/10/2018 Week2
13/10/2018 25/10/2018 Week3
01/10/2017 05/10/2017 Week1
06/10/2017 12/10/2017 Week2
13/10/2017 25/10/2017 Week3

Please help

[swapnilk]

Create a variable vWeekNumber,

vWeekNumber =  if(Date(Week_st ) = Date(01/10/2018) and Date(Week_end ) = Date (05/10/2018) , Week1, if(Date(Week_st ) = Date(06/10/2018) and Date(Week_end ) = Date (12/10/2018) , Week2, if(Date(Week_st ) = Date(13/10/2018) and Date(Week_end ) = Date (25/10/2018) , Week3)))

[PrashantSangle]

If you already have week in source data then why you want to repeat it??

Sorry but I am not getting your question. Your source data and expected output look same except it's repeatation.

Regards,

Prashant Sangle

[viveksingh]

Thanks for the reply. This week_st and week_end are alvailable for 2018. I have to populate week numbers for 2017 also. So to get 2017 dates i have used addmonths() function. Now in my week_st and week_end have 2018 and 2017. But I want add week numbers to 2017 also

[Anonymous]

Hello Vivek, 

I don't see a difference in your data and the requirement. Please rectify.

I assume you have two dates column (Week_st, Week_end) and you need month wise week number as your output.

Use the following code:

T1:
load * Inline
[
Week_st,Week_end
01/10/2018,07/10/2018
08/10/2018,14/10/2018
15/10/2018,21/10/2018
22/10/2018,28/10/2018
];

T2:
load Week_st, Week_end,
'Week'&
if(week(date#(Week_st,'DD/MM/YYYY'))-week(monthstart(date#(Week_st,'DD/MM/YYYY')))+1>1,
week(date#(Week_st,'DD/MM/YYYY'))-week(monthstart(date#(Week_st,'DD/MM/YYYY')))+1,
'1')
as week
Resident T1;

drop Table T1;

Regards,

Amardeep.

[viveksingh]

Thanks for the reply. Yes, my source have week numbers but it is onky for 2018. I need for 2017 also.
In my report I have to show report on weekly basis. If we pivot, under week1, i’m ge Only 2018. But i need 2018 and 2017 data together under weeks

[PrashantSangle · Accepted solution]

Is date for year 2017 already present in your data? do you have any other catch than this??

Can you share more sample data. try below 

Load week_st,

week_end,

week

from  tableName;

concatenate

Load AddYears(week_st,-1) as week_st,

AddYears(week_end,-1) as week_end,

week

from  tableName;

[viveksingh]

I have main data for all years.
But calandar will be defined only for current year like week_st and week_end. I can get 2017 calndar using addmonths() function for the same dates period for another’s year. You just used addyears() and I used addmonths() thats fine. Now how can I add week numbers to 2017

[viveksingh]

Attached is the weekly report I’m expecting

[PrashantSangle]

Have you tried that script

It will work. Week is already get added as per current year week.