Customize Week number

[Karim_Khan]

Hi Team,

   I wnat to customize the week number on the basis of belwo code.

WeekName(DATE,0,-1) AS WEEKNAME

It is giving the weekname as

2016/01

2016/02

2016/03

2016/04

2016/05

2016/06

2016/07

2016/08

2016/09

2016/10

2016/11

2016/12

2016/13

2016/14

2016/15

2016/16

2015/01

2015/02

2015/03

2015/04

2015/05

       And I am able to get the week number as WEEK1,WEEK2,WEEK3,WEEK4 or WEEK5 dependign upon the months by usign below code

'Week' & Ceil(Day(DATE)/7) AS FIN_WEEK     as FIN_WEEK

But I want to cpnvert the above WEEKNAME into FIN_WEEK

Regards,

KK

[Anil_Babu_Samineni]

What was the expected output, can you provide two or three values

[Karim_Khan]

Pls check the attachment

[Anil_Babu_Samineni]

Sorry, I am using mobile

Anyway, Try this

'WEEK'& Week([CURRENT WEEK FORMAT]) as [EXPECTED WEEK FORMAT]

OR

'WEEK'& Right([CURRENT WEEK FORMAT],1) as [EXPECTED WEEK FORMAT]

[Anil_Babu_Samineni]

'Try This too

'WEEK'& Week(If([CURRENT WEEK FORMAT] = Right([CURRENT WEEK FORMAT],2), '10', '4' OR '5', [CURRENT WEEK FORMAT])) as [EXPECTED WEEK FORMAT]

[susovan]

You can also try this,

Table1:

LOAD [CURRENT WEEK FORMAT]

FROM

Table1.xlsx

(ooxml, embedded labels, table is Sheet1);

Table2:

LOAD *, Right([CURRENT WEEK FORMAT],2) as WEEK Resident Table1;

Concatenate

Table3:

LOAD *,

if(WEEK='01','Week1',if(WEEK='02','Week2',if(WEEK='03','Week3',if(WEEK='04','Week4',if(WEEK='05','Week5',

if(WEEK='06','Week6',if(WEEK='07','Week7',if(WEEK='08','Week8',if(WEEK='09','Week9',if(WEEK='10','Week10',if(WEEK='12','Week12',

if(WEEK='13','Week13',if(WEEK='14','Week14',if(WEEK='15','Week15')))))))))))))) as @_Week Resident Table2;

[Anil_Babu_Samineni]

Sometimes, Weeks are upto 53 then What you do? I think this is not a correct way, Please correct me if i am worong. Instead of IF we can use Pick and Match to resolve. IF is not good for performance

[susovan]

Hi Anil,

May be like this,

Table1:

LOAD [CURRENT WEEK FORMAT]

FROM

Table1.xlsx

(ooxml, embedded labels, table is Sheet1);

Table2:

LOAD *, Right([CURRENT WEEK FORMAT],2) as WEEK Resident Table1;

Concatenate

Table3:

LOAD *,

'Week' &AutoNumber(WEEK) as Auto_Number  Resident Table2;