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.
