topic Re: Dynamic update variable to use in Where clause in App Development

Dynamic update variable to use in Where clause

johngouws — Wed, 05 Oct 2022 06:46:31 GMT

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.

Re: Dynamic update variable to use in Where clause

MayilVahanan — Wed, 05 Oct 2022 07:02:48 GMT

Try like below, hope it helps

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;

Re: Dynamic update variable to use in Where clause

justISO — Wed, 05 Oct 2022 07:09:52 GMT

Hi, what about instead of

SET vReportWeek = 1;

use

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

Re: Dynamic update variable to use in Where clause

johngouws — Wed, 05 Oct 2022 07:27:29 GMT

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.