Weird behaviour of and if .. elseif... endif statement (script side) / help required

[OmarBenSalem]

Hi all ( @sunny_talwar )

I'm having some trouble understanding how Qlik is executing my if..else statement.

Let me explain the situation simply:

I reload an application 2 times a week:

1) Friday : on friday; I have the week data from monday to friday 8.00 am

2) Monday : on monday (the next monday) : I have all the last week (till Friday 8.00 am(1) + friday/Saturday/Sunday)

What I aim to do:

Compare loaded data (nb of lines of each table) on Friday vs Next Monday.

To do so, here's how I proceed: (suppose I have only one table)

//This is my table

CSOCount:
Load (TDBH_CSO_SEMAINE) as SemaineConsommation, count(TDBH_CSO_SEMAINE) as CountSemaineConsommation Resident CSOSemaine where [TDBH_CSO_SEMAINE]>=$(vMaxSemCSO)-3
Group by TDBH_CSO_SEMAINE
;

//If it's Friday, I store it in a QVD with the reloadTime 

if PurgeChar( Capitalize(WeekDay(ReloadTime())),'.')='Ven' then

let vReloadVendredi=timestamp(now(),'DDMMYYhhmm');

store CSOCount into [lib://DATA_SOURCES/TDB Dirigeants\Control TDB\QVD_Vendredi\CSOCount_$(vReloadVendredi).qvd](qvd);

//here I store the reload Time into a CSV file to call it later on Monday... calling directly the variable didn't work, I thought this might.. but.. naah..
t:
load '$(vReloadVendredi)' as reloadTime AutoGenerate 1 ;

store t into [lib://DATA_SOURCES/TDB Dirigeants\Control TDB\QVD_Vendredi\ReloadTimeVendredi.csv](txt);

drop Table t;

exit Script

//Now, if it's Monday; I load the table (monday) then call the table I stored (Friday)

ElseIf PurgeChar( Capitalize(WeekDay(ReloadTime())),'.')='Lun' then

Reload:
LOAD
"reloadTime"
FROM [lib://DATA_SOURCES/TDB Dirigeants\Control TDB\QVD_Vendredi\ReloadTimeVendredi.csv]
(txt, utf8, embedded labels, delimiter is '\t', msq);

let vReloadVendrediSet=peek('reloadTime',0,'Reload');

left Join(CSOCount)

Load SemaineConsommation ,CountSemaineConsommation as CountSemaineConsommationVendredi
from [lib://DATA_SOURCES/TDB Dirigeants\Control TDB\QVD_Vendredi\CSOCount_$(vReloadVendrediSet).qvd](qvd);

exit script
EndIf

=> This works PERFECTLY when I execute the script manually from the data load editor ! 

Qlik will search for the QVD with the friday reload time stored in the CSV file $(vReloadVendrediSet) 

And I'll have sthing like this:

BUT, when the reload TASK is TRIGGERED, this doesn't WORK!!!!

Qlik stores the reloadTime of MONDAY into the Friday one (while it should not !!!) because it executes the whole Friday segment (Friday in french = Vendredi...) Ven thus mean Friday

see:

And so the whole thing is messed up :

But, when I reload the APP manually, the if statement is well executed...

Don't understand why this happens..

Maybe the QMC is in Enligh? thus it does not understand the Ven and Lun part? and I should add Mon and Fri in my if condition?

I'll try that with hope someone could help me understand this ...

[pradosh_thakur]

Hi Omar,

Why not try using weekday() output as number and not string (4 for friday) , I will check the exit script after drop table t as well as it doesn't allow the if .... end if to be completed. May be use one exit script if required at the last . 

-Pradosh

[marcus_sommer]

You would need to ensure that the server used the same variables/settings like your local desktop client. But you could avoid this by following the suggestion from pradosh_thakur with the numerical value of weekday(). Further I wouldn't use the csv else just storing the timestamp in a variable - I assume it didn't work for you because of a wrong syntax and you mistakes $(var) and '$(var)' by calling it.

- Marcus

[sunny_talwar]

I am not 100% sure I understand the whole problem here, but I think ReloadTime() displays the date and time related to the last successful reload in the script. So, if the last successful reload was on Friday, when you run this on Monday... you will still see the reload time from Friday.

Why don't you try this instead

If PurgeChar(Capitalize(WeekDay(Today(1))), '.') = 'Ven' THEN

This way you would be checking today's WeekDay

[OmarBenSalem]

I also think that the QMC's definiton of the weekday is not the same as in the load script editor (in french) since the QMC is by default defined in English.

Thus, I've changed my conditon to the follow: (with hopes it would work) since it works just fine when I execute the script in the data load editor.

// if PurgeChar( Capitalize(WeekDay(ReloadTime())),'.')='Ven' or PurgeChar( Capitalize(WeekDay(ReloadTime())),'.')='Fri' then
if PurgeChar( Capitalize(WeekDay(ReloadTime(),0)),'.')=4 then

For the exit part; it does allow the script to continue the execution since it's not called on monday; it's just added to "be sure" Qlik does not execute Monday script on Friday... (I was fed up with how things went so I've added some "not that useful" controls

[OmarBenSalem]

I see that u havn't understood the whole thing Sunny 😛 

Not a problem ! thanks anyway