Change of variable value - Source file unavailable - Reload Fails

[jpjust]

Hi

I have this variable in my script - LET FileNameC4000 = 'C4000_WK'& num(Week(Today ()), '00') &'.xlsx';
The result of the variable is C4000_WK04.xlsx

And this variable get fed into the load script as below.

LOAD

FieldA
FieldB
FROM [lib://filepath/$(FileNameC4000)]
(ooxml, embedded labels, table is $(TabNameC4000))

The source file is available at the file path as C4000_WK04.xlsx

Now, after Saturday the variable value changes to C4000_WK05.xlsx because of the 5'th week.
The file gets uploaded only on Monday, so since then the reload of this script fails due to the file not found error because it looks for C4000_WK05.xlsx

Can some one please advice on how to mitigate this error? I think to exit the script gracefully if the file is not yet available for the 5'th week but how to accomplish it?

Thanks

[lironbaram]

Hi,
you can take two approaches:

1. before the reload ,  use this call
   

   set errormode=0;​

   this setting, basically tells the qlik engine to disregard any error and continue
   so after this part, I'll recommend returning the variable to the default value.
   

   set errormode=1;​

2. Change the date, so if you move the date 2 days back using this expression 
   

    LET FileNameC4000 = 'C4000_WK'& num(Week(Today ()-2), '00') &'.xlsx';​

   using this expression the week number will change only on Monday 

[MayilVahanan]

Hi

You can try like below

Let vWeekDay = WeekDay(Today(1));

If vWeekDay = 'Sat' or  vWeekDay = 'Sun' then

LET FileNameC4000 = 'C4000_WK'& num(Week(Today (1)-2), '00') &'.xlsx';

Else

LET FileNameC4000 = 'C4000_WK'& num(Week(Today (1)), '00') &'.xlsx';

End If

LOAD

FieldA
FieldB
FROM [lib://filepath/$(FileNameC4000)]
(ooxml, embedded labels, table is $(TabNameC4000));

So that, it won't throw error on Sat n Sun, it will fetch last week file

[jpjust]

Thank you so much!!

[jpjust]

Thank you so much Mayil