Dynamic update variable to use in Where clause

[johngouws]

Good morning.

I have a case where I need to load data for specific customers. The difference from the 'norm' is that for discussion purposes, different codes are loaded on each Monday of the month. I have this working manually when I set a variable before the load. eg: SET vReportWeek = 1;
However, I would like to make this dynamic. I have the code to identify the relevant Monday, but when I try to use the value in the varaiable is where I am having the issue.

My Load script is below with the variable set manually.

SET vReportWeek = 1;
//SET vReportWeek = "=max(Week_no)";

t0:
Load * INLINE [
Code , Monday1 ,Monday2 ,Monday3 ,Monday4
AAA000 , x , , ,x
AAB000 , ,x , ,x
AAC000 , , ,x ,x
AAD000 , x , , ,x
AAE000 , ,x , ,x
];

NoConcatenate

t1:
Load
*,
if(DayName(MonthStart(Today()),Mod(7-WeekDay(MonthStart(Today())),7)) = Today(), 1,
if(DayName(MonthStart(Today()),Mod(7-WeekDay(MonthStart(Today())),7)+7) = Today(), 2,
if(DayName(MonthStart(Today()),Mod(7-WeekDay(MonthStart(Today())),7)+14) = Today(), 3,
if(DayName(MonthStart(Today()),Mod(7-WeekDay(MonthStart(Today())),7)+21) = Today(), 4,
if(DayName(MonthStart(Today()),Mod(7-WeekDay(MonthStart(Today())),7)+28) = Today(), 4,
1 //Normally 0 - 1 for testing
))))) as Week_no
Resident t0;
Drop Table t0;

NoConcatenate

t2:
Load
*
Resident t1
Where Monday$(vReportWeek) = 'x';
Drop Table t1;

Thank you - I appreciate any advice.

[MayilVahanan · Accepted solution]

Hi

Try like below, hope it helps

t1:
Load
*,
if(DayName(MonthStart(Today()),Mod(7-WeekDay(MonthStart(Today())),7)) = Today(), 1,
if(DayName(MonthStart(Today()),Mod(7-WeekDay(MonthStart(Today())),7)+7) = Today(), 2,
if(DayName(MonthStart(Today()),Mod(7-WeekDay(MonthStart(Today())),7)+14) = Today(), 3,
if(DayName(MonthStart(Today()),Mod(7-WeekDay(MonthStart(Today())),7)+21) = Today(), 4,
if(DayName(MonthStart(Today()),Mod(7-WeekDay(MonthStart(Today())),7)+28) = Today(), 4,
1 //Normally 0 - 1 for testing
))))) as Week_no
Resident t0;
Drop Table t0;

temp:
Load Max(Week_no) as MaxWk Resident t1;

LET vReportWeek = peek('MaxWk');

TRACE vReportWeek -- $(vReportWeek);

NoConcatenate

t2:
Load
*
Resident t1
Where Monday$(vReportWeek) = 'x';
Drop Table t1, temp;

[justISO]

Hi, what about instead of

SET vReportWeek = 1;

use

LET vReportWeek = Ceil(Day(Today())/7);

[johngouws]

Thank you @MayilVahanan . 
That is exactly what I needed. ✔ I appreciate it very much. 

@justISO - Your solution also works, but the /7 ties me into the first week. The solution from @MayilVahanan I think gives me more options. 

Thanks very much.