Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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..
Try this:
Table:
LOAD DayOfYear,
SnapShotDate,
Week,
Year,
YearWeek,
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]
FROM
[week bucket output.xls]
(biff, embedded labels, table is Sheet1$);
The code will need some modifications based on your need.
HTH
Best,
Sunny
@Massimo Grossi
Not sure if I understand Vinay, but can't you just do this:
If(Even(Week), YearWeek & YearWeek + 1, YearWeek - 1 & YearWeek) as NewColumn
Sunny That does not work , what i wanted to is i want to group two weeks at a time as new dimension..
or this if you need & between them:
If(Even(Week), YearWeek & '&' & YearWeek + 1, YearWeek - 1 & '&' & YearWeek) as NewColumn
remember for one week means you will include 7 days, 1 month means 30 or 31 days.. now i want to create a new dimension with two week numbers
any help
Vinay isn't this what you want???
Script:
Table:
LOAD DayOfYear,
SnapShotDate,
Week,
Year,
YearWeek,
If(Even(Week) = -1, YearWeek & '&' & (YearWeek + 1), YearWeek - 1 & '&' & YearWeek) as NewColumn
FROM
[week bucket output.xls]
(biff, embedded labels, table is Sheet1$);
Wow amazing ...In the similar manner can we create 3 more columns with 3 week , 4 week and 5 week buckets respectively..
I think it can be done. Just need to know how they will be grouped?
my current logic for 2 weeks was if week is even week then week & week + 1, but if its odd week then week-1 and week.
What are the logic for other combinations?