Need HELP with if else logic !!

[OmarBenSalem]

Hi experts ( stalwar1‌)

Here's what I want to do :

I have 4 dates : thus 4 URLs = 4 variables to call these 4 dates(vD1, vD2 vM1 and vY1, not exactly written as follow, but u understand I guess).

The resulting Table is FX tables = result of 4 FX tables each corresponding to a date.

Now, sometimes, one of the dates are missing ! (the file does not exist => when trying to reload, we'll have an error or the file exists but contains only NULL values=Contains NOTHING)

So a control have to be made as follow:

If the FX RATE of Y-1 is not available, replace it by null values and continue the execution of the script (import the 3 other Dates)

if the FX RATE of D1 or D2 or M1 does not exist (MISSING FILE or NULL VALUES)

we have to interrupt the execution of the script immediately.

To do this, I've used the:

ErrorMode=0 to force qlik to keep the reloading process in case of error (missing files..,

scriptErrorCount to track the errors and thus know what to do,

and a bunch of if ...elses

But Im having a semantic error :

PLEASE, help ! I know I'll be sharing some long code; but I can't figure out what I'm doing wrong...

ANY HINT / correction would be very welcome

------------------------------------------------------------------------------------------------------------------------------------------------

LET ErrorMode=0;

//********************* FX RATE Y1****************

LIB CONNECT TO 'Get Fx_Rate';

RestConnectorMasterTable:

SQL SELECT

"asOfDate",

"baseCurrency",

"pivotCurrency",

"fxRate"

FROM JSON (wrap on) "root"

WITH CONNECTION (

URL "$(vURLY1Fx)"

)

;

fx:

LOAD

    [asOfDate] AS [AsOf_Date],

[baseCurrency] AS [Currency],

[pivotCurrency] AS [Pivot_Currency],

[fxRate] AS [FX_Rate]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

LET nNbrRowsFXyear = NoOfRows('fx');

if

scriptErrorCount >0 or $(nNbrRowsFXyear)=0

then

drop table fx;

//********************* FX RATE Y1****************

LIB CONNECT TO 'Get Fx_Rate';

RestConnectorMasterTable:

SQL SELECT

"asOfDate",

"baseCurrency",

"pivotCurrency",

"fxRate"

FROM JSON (wrap on) "root"

WITH CONNECTION (

URL "$(vURLY1Fx)"

)

;

fx:

LOAD Null() AS [AsOf_Date],

Null() AS [Currency],

Null() AS [Pivot_Currency],

Null() AS [FX_Rate]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

else

//********************* FX RATE D1****************

LIB CONNECT TO 'Get Fx_Rate';

RestConnectorMasterTable:

SQL SELECT

"asOfDate",

"baseCurrency",

"pivotCurrency",

"fxRate"

FROM JSON (wrap on) "root"

WITH CONNECTION (

URL "$(vURLD1Fx)"

)

;

fx1:

LOAD [asOfDate] AS [AsOf_Date],

[baseCurrency] AS [Currency],

[pivotCurrency] AS [Pivot_Currency],

[fxRate] AS [FX_Rate]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

LET nNbrRowsFX1 = NoOfRows('fx1');

//********************* FX RATE D2****************

LIB CONNECT TO 'Get Fx_Rate';

RestConnectorMasterTable:

SQL SELECT

"asOfDate",

"baseCurrency",

"pivotCurrency",

"fxRate"

FROM JSON (wrap on) "root"

WITH CONNECTION (

URL "$(vURLD2Fx)"

)

;

NoConcatenate

fx2:

LOAD [asOfDate] AS [AsOf_Date],

[baseCurrency] AS [Currency],

[pivotCurrency] AS [Pivot_Currency],

[fxRate] AS [FX_Rate]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

LET nNbrRowsFX2 = NoOfRows('fx2');

//******************** FX RATE M1****************

LIB CONNECT TO 'Get Fx_Rate';

RestConnectorMasterTable:

SQL SELECT

"asOfDate",

"baseCurrency",

"pivotCurrency",

"fxRate"

FROM JSON (wrap on) "root"

WITH CONNECTION (

URL "$(vURLM1Fx)"

)

;

NoConcatenate

fx3:

LOAD [asOfDate] AS [AsOf_Date],

[baseCurrency] AS [Currency],

[pivotCurrency] AS [Pivot_Currency],

[fxRate] AS [FX_Rate]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

LET nNbrRowsFX3 = NoOfRows('fx3');

//********************* FX RATE Y1****************

LIB CONNECT TO 'Get Fx_Rate';

RestConnectorMasterTable:

SQL SELECT

"asOfDate",

"baseCurrency",

"pivotCurrency",

"fxRate"

FROM JSON (wrap on) "root"

WITH CONNECTION (

URL "$(vURLY1Fx)"

)

;

Noconcatenate

fx4:

LOAD [asOfDate] AS [AsOf_Date],

[baseCurrency] AS [Currency],

[pivotCurrency] AS [Pivot_Currency],

[fxRate] AS [FX_Rate]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

if scriptErrorCount >0 or $(nNbrRowsFX1)=0  or $(nNbrRowsFX2)=0 or $(nNbrRowsFX3)=0

then

LET ErrorMode=1;

exit Script;

else

NoConcatenate

fx:

Load * Resident fx1;

load * Resident fx2;

Load * Resident fx3;

load * Resident fx4;

drop Tables fx1,fx2,fx3,fx4;

endif

endif

------------------------------------------------------------------------------------------------------------------------------------------------

Thank you for your time !

[sunny_talwar]

Were you not doing NoOfRows() check? That is where I thought you would do it