Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Chanty4u
MVP
MVP

Creation of a weeknumber based on the existing columns

Hi All,

Can anyone help with the code to achieve the WEEK_NUMBER like 201601,201602,201603,....201652. Based on either Date or Week

column.

Example data for the Date and Week columns are

 

Thanks in Advance

1 Solution

Accepted Solutions
thkarner
Partner - Creator III
Partner - Creator III

Hi,

this should it be. I used a preceding load before the inline-load.

If this helps you please mark it as helpful or answerd.

KR Thomas

Table:

LOAD

  *,

  Year(Date#(Mid(Date,7), 'YYYY-MM-DD')) & Num(Week(Date#(Mid(Date,7), 'YYYY-MM-DD')), '00') AS Date_Week,

  Year(Date#(Mid(Week,4), 'MM-DD-YYYY')) & Num(Week(Date#(Mid(Week,4), 'MM-DD-YYYY')), '00') AS Week_Week;

LOAD * INLINE [

    Date, Week

    SAT - 2015-06-27, Wk 06-22-2015

    FRI - 2015-11-06, Wk 11-02-2015

    SUN - 2015-06-21, Wk 06-15-2015

    SUN - 2015-09-20, Wk 09-14-2015

    FRI - 2015-12-18, Wk 12-14-2015

    FRI - 2015-12-18, Wk 12-14-2015

    WED - 2016-03-02, Wk 02-29-2016

    SUN - 2015-06-21, Wk 06-15-2015

    TUE - 2016-03-01, Wk 02-29-2016

    FRI - 2015-11-06, Wk 11-02-2015

    TUE - 2015-08-11, Wk 08-10-2015

    SUN - 2015-09-20, Wk 09-14-2015

    FRI - 2015-11-06, Wk 11-02-2015

];

View solution in original post

3 Replies
sunny_talwar

What is the weeknumber for the same data you have provided? I guess what would be the output for the given data?

thkarner
Partner - Creator III
Partner - Creator III

Hi,

this should it be. I used a preceding load before the inline-load.

If this helps you please mark it as helpful or answerd.

KR Thomas

Table:

LOAD

  *,

  Year(Date#(Mid(Date,7), 'YYYY-MM-DD')) & Num(Week(Date#(Mid(Date,7), 'YYYY-MM-DD')), '00') AS Date_Week,

  Year(Date#(Mid(Week,4), 'MM-DD-YYYY')) & Num(Week(Date#(Mid(Week,4), 'MM-DD-YYYY')), '00') AS Week_Week;

LOAD * INLINE [

    Date, Week

    SAT - 2015-06-27, Wk 06-22-2015

    FRI - 2015-11-06, Wk 11-02-2015

    SUN - 2015-06-21, Wk 06-15-2015

    SUN - 2015-09-20, Wk 09-14-2015

    FRI - 2015-12-18, Wk 12-14-2015

    FRI - 2015-12-18, Wk 12-14-2015

    WED - 2016-03-02, Wk 02-29-2016

    SUN - 2015-06-21, Wk 06-15-2015

    TUE - 2016-03-01, Wk 02-29-2016

    FRI - 2015-11-06, Wk 11-02-2015

    TUE - 2015-08-11, Wk 08-10-2015

    SUN - 2015-09-20, Wk 09-14-2015

    FRI - 2015-11-06, Wk 11-02-2015

];

thkarner
Partner - Creator III
Partner - Creator III

Hi Chanty 4u,

you marked my answer as helpful. What is missing to mark it as correct answer?

Do you need any explanation?