Want to create Week Bucket Dimension in the script

[Anonymous]

Hi Experts,

My requirement is little strange but you need your help. I have snapshot date column, YearWeek column, Now i need to create a column which is the combination of two weeks. For example if my first week in 2014 starts from aug-17-2014 (sunday ) to aug-24-2015 (sunday) and the week number is 201434. Now i wanted  a new column which will include two weeks start date is aug-17-2014 to aug-31-2014 and the value in the column should look like this 201434&201435... see the attached QVW with all the dates...and attached excel sheet with some sample data how the output should look like..

[Anonymous]

Yaar one last thing yaar.. sorry to disturb your sleep... can i have script for 5, 6,7 8, 9 and 10 weeks bucket.. i know i am asking too much but..

[sunny_talwar]

My daughter is keeping me up and not you.. so you don't have anything to worry about .

I am working on it

[Anonymous]

super cool .. she and i are friends.. we have each others back .

[Anonymous]

Any luck brother..

[antoniotiman]

Hi Vinay,

another way without changing script.

You can select YearWeek and select Num of columns by slider.

If You want to see all weeks then remove set analysis from expression

Regards,

Antonio

[Anonymous]

hi ANtonia,

very interesting, that is really good. But i have plenty of data.. i prefer to in the backend.. Thanks a lot for your help brother..

[Anonymous]

you are always a great help for me .

[sunny_talwar]

Try this:

Table:

LOAD DayOfYear,

    SnapShotDate,

    Week,

    Year,

    YearWeek

FROM

[Calendar Week.xls]

(biff, embedded labels, table is Sheet1$);

[Final Table]:

LOAD *,

  If(Even(Week) = -1, YearWeek & '&' & (YearWeek + 1), YearWeek - 1 & '&' & YearWeek) as [2Weeks],

    If(Mod(AutoNumber(Week), 3) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

    If(Mod(AutoNumber(Week), 3) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

      (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek)) as [3Weeks],

    If(Mod(AutoNumber(Week), 4) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

    If(Mod(AutoNumber(Week), 4) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

    If(Mod(AutoNumber(Week), 4) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

      (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek))) as [4Weeks],

    If(Mod(AutoNumber(Week), 5) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4),

    If(Mod(AutoNumber(Week), 5) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

    If(Mod(AutoNumber(Week), 5) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

    If(Mod(AutoNumber(Week), 5) = 4, (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

      (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & (YearWeek))))) as [5Weeks],

    If(Mod(AutoNumber(Week), 6) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5),

    If(Mod(AutoNumber(Week), 6) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4),

    If(Mod(AutoNumber(Week), 6) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

    If(Mod(AutoNumber(Week), 6) = 4, (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

    If(Mod(AutoNumber(Week), 6) = 5, (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

      (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & (YearWeek)))))) as [6Weeks],

    If(Mod(AutoNumber(Week), 7) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6),

    If(Mod(AutoNumber(Week), 7) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5),

    If(Mod(AutoNumber(Week), 7) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4),

    If(Mod(AutoNumber(Week), 7) = 4, (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

    If(Mod(AutoNumber(Week), 7) = 5, (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

    If(Mod(AutoNumber(Week), 7) = 6, (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

      (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & (YearWeek))))))) as [7Weeks],

    If(Mod(AutoNumber(Week), 😎 = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6) & '&' & (YearWeek + 7),

    If(Mod(AutoNumber(Week), 😎 = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6),

    If(Mod(AutoNumber(Week), 😎 = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5),

    If(Mod(AutoNumber(Week), 😎 = 4, (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4),

    If(Mod(AutoNumber(Week), 😎 = 5, (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

    If(Mod(AutoNumber(Week), 😎 = 6, (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

    If(Mod(AutoNumber(Week), 😎 = 7, (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

      (YearWeek - 7) & '&' & (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & (YearWeek)))))))) as [8Weeks],

    If(Mod(AutoNumber(Week), 9) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6) & '&' & (YearWeek + 7) & '&' & (YearWeek + 8),

    If(Mod(AutoNumber(Week), 9) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6) & '&' & (YearWeek + 7),

    If(Mod(AutoNumber(Week), 9) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6),

    If(Mod(AutoNumber(Week), 9) = 4, (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5),

    If(Mod(AutoNumber(Week), 9) = 5, (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4),

    If(Mod(AutoNumber(Week), 9) = 6, (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

    If(Mod(AutoNumber(Week), 9) = 7, (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

    If(Mod(AutoNumber(Week), 9) = 8, (YearWeek - 7) & '&' & (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

      (YearWeek - 😎 & '&' & (YearWeek - 7) & '&' & (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & (YearWeek))))))))) as [9Weeks],

    If(Mod(AutoNumber(Week), 10) = 1, YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6) & '&' & (YearWeek + 7) & '&' & (YearWeek + 😎 & '&' & (YearWeek + 9),

    If(Mod(AutoNumber(Week), 10) = 2, (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6) & '&' & (YearWeek + 7) & '&' & (YearWeek + 8),

    If(Mod(AutoNumber(Week), 10) = 3, (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6) & '&' & (YearWeek + 7),

    If(Mod(AutoNumber(Week), 10) = 4, (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5) & '&' & (YearWeek + 6),

    If(Mod(AutoNumber(Week), 10) = 5, (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4) & '&' & (YearWeek + 5),

    If(Mod(AutoNumber(Week), 10) = 6, (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3) & '&' & (YearWeek + 4),

    If(Mod(AutoNumber(Week), 10) = 7, (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2) & '&' & (YearWeek + 3),

    If(Mod(AutoNumber(Week), 10) = 8, (YearWeek - 7) & '&' & (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1) & '&' & (YearWeek + 2),

    If(Mod(AutoNumber(Week), 10) = 9, (YearWeek - 😎 & '&' & (YearWeek - 7) & '&' & (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & YearWeek & '&' & (YearWeek + 1),

        (YearWeek - 9) & '&' & (YearWeek - 😎 & '&' & (YearWeek - 7) & '&' & (YearWeek - 6) & '&' & (YearWeek - 5) & '&' & (YearWeek - 4) & '&' & (YearWeek - 3) & '&' & (YearWeek - 2) & '&' & (YearWeek - 1) & '&' & (YearWeek)))))))))) as [10Weeks]

Resident Table

Order By SnapShotDate;

DROP Table Table;

[Anonymous]

Thanks a bunch.. now you can sleep brother..