topic Re: Want to create Week Bucket Dimension in the script in QlikView

Want to create Week Bucket Dimension in the script

Anonymous — Fri, 07 Aug 2015 19:04:44 GMT

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

Re: Want to create Week Bucket Dimension in the script

Anonymous — Fri, 07 Aug 2015 19:07:56 GMT

MRKachhiaIMP‌

@Massimo Grossi

Re: Want to create Week Bucket Dimension in the script

sunny_talwar — Fri, 07 Aug 2015 19:08:24 GMT

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

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

Re: Want to create Week Bucket Dimension in the script

Anonymous — Fri, 07 Aug 2015 19:12:10 GMT

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

sunny_talwar — Fri, 07 Aug 2015 19:12:46 GMT

or this if you need & between them:

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

Re: Want to create Week Bucket Dimension in the script

Anonymous — Fri, 07 Aug 2015 19:15:11 GMT

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

Re: Want to create Week Bucket Dimension in the script

Anonymous — Fri, 07 Aug 2015 19:22:36 GMT

any help

Re: Want to create Week Bucket Dimension in the script

sunny_talwar — Fri, 07 Aug 2015 19:25:16 GMT

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

Re: Want to create Week Bucket Dimension in the script

Anonymous — Fri, 07 Aug 2015 19:34:58 GMT

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

sunny_talwar — Fri, 07 Aug 2015 19:37:25 GMT

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?

Re: Want to create Week Bucket Dimension in the script

Anonymous — Fri, 07 Aug 2015 19:42:07 GMT

Please see the output how it should look like

Re: Want to create Week Bucket Dimension in the script

sunny_talwar — Fri, 07 Aug 2015 19:54:15 GMT

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

Re: Want to create Week Bucket Dimension in the script

Anonymous — Fri, 07 Aug 2015 20:00:27 GMT

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

Re: Want to create Week Bucket Dimension in the script

sunny_talwar — Fri, 07 Aug 2015 20:03:49 GMT

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

Re: Want to create Week Bucket Dimension in the script

Anonymous — Fri, 07 Aug 2015 20:11:31 GMT

pLEASE check this QVW file am i missing anything

Re: Want to create Week Bucket Dimension in the script

sunny_talwar — Fri, 07 Aug 2015 20:15:32 GMT

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.

Re: Want to create Week Bucket Dimension in the script

Anonymous — Fri, 07 Aug 2015 20:21:58 GMT

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

Re: Want to create Week Bucket Dimension in the script

Anonymous — Fri, 07 Aug 2015 20:24:30 GMT

yup got it brother , thanks a lot .

Re: Want to create Week Bucket Dimension in the script

sunny_talwar — Fri, 07 Aug 2015 20:28:34 GMT

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 *,