topic Re: Change of variable value - Source file unavailable - Reload Fails in App Development

Change of variable value - Source file unavailable - Reload Fails

jpjust — Thu, 27 Jan 2022 02:05:41 GMT

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

Re: Change of variable value - Source file unavailable - Reload Fails

lironbaram — Thu, 27 Jan 2022 07:17:53 GMT

Hi,
you can take two approaches:

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;
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

Re: Change of variable value - Source file unavailable - Reload Fails

MayilVahanan — Thu, 27 Jan 2022 10:10:24 GMT

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

Re: Change of variable value - Source file unavailable - Reload Fails

jpjust — Thu, 27 Jan 2022 18:52:11 GMT

Thank you so much!!

Re: Change of variable value - Source file unavailable - Reload Fails

jpjust — Thu, 27 Jan 2022 18:52:31 GMT

Thank you so much Mayil