Solved: Re: Want to create Week Bucket Dimension in the sc... - Page 2 - Qlik Community

Anonymous · ‎2015-08-07

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 · ‎2015-08-07

Please see the output how it should look like

sunny_talwar · ‎2015-08-07

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

Anonymous · ‎2015-08-07

For the 3rd week and 4th week bucket the week should should start from week 201434 ...not from 201431... like dynamically it should take the minimum week as first week..

sunny_talwar · ‎2015-08-07

From your sample I am seeing 201434 as the minimum week:

Seems like this is what you want? Let me know if I am missing something....

Anonymous · ‎2015-08-07

pLEASE check this QVW file am i missing anything

sunny_talwar · ‎2015-08-07

I don't have a licensed version of QlikView, so can't really open the file you are sharing. Can you share the script, if you have made any changes to it and a screenshot of the table box object?

One thing to note here is that, you will need your data to be sorted in a correct order. So if it isn't sorted in Ascending order by SnapShotDate, I would recommend doing the 2Weeks, 3Weeks and 4Weeks calculation in preceding load or resident load with the order by statement.

Anonymous · ‎2015-08-07

Attached is the excel sheet and scripy

Test:

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

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

Anonymous · ‎2015-08-07

yup got it brother , thanks a lot .

sunny_talwar · ‎2015-08-07

See if this is what you want:

Table:

LOAD DayOfYear,

SnapShotDate,

Week,

Year,

YearWeek

FROM

[Calendar Week.xls]

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

[Final Table]:

LOAD *,