Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

vinay_bangari
Valued Contributor III

Want to create Week Bucket Dimension in the script

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..

1 Solution

Accepted Solutions

Re: Want to create Week Bucket Dimension in the script

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

28 Replies
vinay_bangari
Valued Contributor III

Re: Want to create Week Bucket Dimension in the script

MRKachhiaIMP

@Massimo Grossi

Re: Want to create Week Bucket Dimension in the script

Not sure if I understand Vinay, but can't you just do this:

If(Even(Week), YearWeek & YearWeek + 1, YearWeek - 1 & YearWeek) as NewColumn

vinay_bangari
Valued Contributor III

Re: Want to create Week Bucket Dimension in the script

Sunny That does not work , what i wanted to is i want to group two weeks at a time as new dimension..

Re: Want to create Week Bucket Dimension in the script

or this if you need & between them:

If(Even(Week), YearWeek & '&' & YearWeek + 1, YearWeek - 1 & '&' & YearWeek) as NewColumn

vinay_bangari
Valued Contributor III

Re: Want to create Week Bucket Dimension in the script

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

vinay_bangari
Valued Contributor III

Re: Want to create Week Bucket Dimension in the script

any help

Re: Want to create Week Bucket Dimension in the script

Vinay isn't this what you want???

Capture.PNG

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$);

vinay_bangari
Valued Contributor III

Re: Want to create Week Bucket Dimension in the script

Wow amazing ...In the similar manner can we create 3 more columns with 3 week , 4 week and 5 week buckets respectively..

Re: Want to create Week Bucket Dimension in the script

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?

Community Browser