Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
stalwar1 -- Please have a look if you can help in this ..
I have a scenario, where I want to get Latest 13 weeks and my data is at week level.
Week Id is in the format 201601..201625..201652.
Currently I am using the logic [where Week ID > Max(Week ID)-13], as of now my Max Week id 201652. So I am able to get latest 13 weeks.
But in future, I can have weeks as 201701..201702.. likewise.
In this case my above logic will fail.
Need a logic to achieve this at SCRIPT LEVEL which will be able to handle future Week IDs too.
May be like this
FACT:
LOAD [Week ID],
....
FROM ....;
Max:
LOAD Max([Week ID]) as MaxWeekID
Resident FACT;
LET vMaxWeekID = Peek('MaxWeekID');
FINAL_FACT
NoConcatenate
LOAD *
RESIDENT FACT
WHERE [WEEK ID] > $(vMaxWeekID) - 13;
DROP Tables FACT, Max;
you can try this with little change to your script
Autonumber(WeekID) as WeekSerial
Where WeekSerial > Max(WeekSerial)-13