Week Number

[ciaran_mcgowan]

Hi all,

Is there a way to alter the Week() Function with custom numbers?

I have an issue where the first week in the year differs from year to year (up to several weeks difference in some cases). It's for tracking marketing campaigns so some years they start in August, other years can  be November (with non specific date starts). One year was 15/08/2015 and another was 03/11/2012.

Another issue is that these campaigns don't run every week but can in some cases so I need each week to only be incremented if there is data. Here's an example from my current data set from the beginning of 2012:

Campaign	Date	Week
Discount Email Blast	15/08/2012	33
Discount Email Blast	23/08/2012	34
Discount Email Blast	03/09/2012	36
Discount Email Blast	19/09/2012	38

What I need these to read is:

Campaign	Date	Week
Discount Email Blast	15/08/2012	1
Discount Email Blast	23/08/2012	2
Discount Email Blast	03/09/2012	3
Discount Email Blast	19/09/2012	4

Any ideas?

[ciaran_mcgowan · Accepted solution]

Hi Ruben,

I've been trying a few different ways to accomplish what I want and I think I cracked it this morning. Instead of loading all files at once and trying to create a Week Number, I created a loop to only load one at a time.

temp1:
LOAD * FROM ;

temp2:
LOAD DISTINCT Campaign Resident temp1;

DROP TABLE temp1;

FOR i = 0 TO NoOfRows('temp2')
LET vCampaign = Peek('Campaign', $(i), temp2);

Campaign:
LOAD *,
  Campaign & '-' & Week(Date) AS CampaignWeekID
FROM ;

Within the Campaign table, I created a new field called CampaignWeekID. Then I loaded all distinct CampaignWeekID's into another temp table to find out how many distinct weeks there were in each Campaign year (numbers vary from year to year).

CampaignWeekCount:
LOAD DISTINCT CampaignWeekID AS WeekCount Resident Campaign;

Next I create a loop for each distinct week for that year and another temp table to store the results of each loop.

FOR j = 0 TO NoOfRows('CampaignWeekCount')-1
LET vCampaignWeekID = Peek('WeekCount', $(j), CampaignWeekCount);

Campaign_tmp:
LOAD WeekCount,
  '$(CampaignWeekID)' AS CampaignWeekID,
  ('$(j)'+1) AS WeekNum
Resident CampaignWeekCount;

NEXT j

DROP TABLE CampaignWeekCount;

Then I joined the distinct values back to the original table and dropped the temporary one and moved on to the next campaign year.

LEFT JOIN(Campaign)
LOAD DISTINCT
  CampaignWeekID,
  WeekNum
Resident Campaign_tmp;

DROP Table Campaign_tmp;

STORE Campaign INTO ;
DROP TABLE Campaign;

NEXT i

DROP TABLE temp2;

It might seem a little inefficient, but I wasn't having any luck with your approach or by using functions like RowNo() or AutoNumber(). I'm still testing my results but all looks good so far.