Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.