Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
OmarBenSalem

Need HELP with if else logic !!

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 !

10 Replies
sunny_talwar

Where do you see the error?

OmarBenSalem
Author

My bad !

Capture.PNG

+ since the table is not found , LetNowOfRowFX1 is returning NULL and not 0; so I think I should add it in the condition:

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


To maybe:


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

r $(nNbrRowsFX1)='NULL'  or $(nNbrRowsFX2)='NULL' or $(nNbrRowsFX3)='NULL'


Capture.PNG

+ I know have a synthetic key between the fx tables...

What a mess !

sunny_talwar

What is this saying...

Capture.PNG

sunny_talwar

May be use Alt(...., 0) for the definition to convert the null to 0

sunny_talwar

Or you can use Len(Trim()) if the output is a text and not a number. Alt only works on number which is true in your case

OmarBenSalem
Author

I'd need to do that with all the fields of the table.. while I need to control only whether their is data or not (so the better control must be done directly in the noOfRows of a table..

if it's =0 or = Null => no data

Aaah; can't figure out how to do this...

sunny_talwar

I'd need to do that with all the fields of the table..

Why all the fields of the table?

OmarBenSalem
Author

I see what u're trying to say:

I can to sthing like this:

let vFX= len(trim(Peek('AsOf_Date',0,'fx')));

and control as follow with a single mandatory field.

Now, my main issue is; the if else logic..

I can't figure out where to put them...

Is this correct?

load Y1

if

Y1 is missing or Y1=NULL

then continue (don't interrupt the script)

load D1 D2 D3

if one of them is missing

exit script

else

continue the script

end if

end if

OmarBenSalem
Author

Please , to all experts out there : why is this incorrect??

I'm out of IDEAS...

//********************* 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)"

)

;

fx0:

LOAD

    [asOfDate] AS [AsOf_Date],

[baseCurrency] AS [Currency],

[pivotCurrency] AS [Pivot_Currency],

[fxRate] AS [FX_Rate]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

let vFX= len(trim(Peek('AsOf_Date',0,'fx')));

if scriptErrorCount=0 or $(vFX)>0

then

//********************* 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)"

)

;

NoConcatenate

fx1:

LOAD [asOfDate] AS [AsOf_Date],

[baseCurrency] AS [Currency],

[pivotCurrency] AS [Pivot_Currency],

[fxRate] AS [FX_Rate]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

let vFX1= len(trim(Peek('AsOf_Date',0,'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 vFX2= len(trim(Peek('AsOf_Date',0,'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 vFX3= len(trim(Peek('AsOf_Date',0,'fx3')));

if scriptErrorCount >0 or  $(vFX1)=0 or  $(vFX2)=0 or  $(vFX3)=0

then

LET ErrorMode=1;

exit Script;

  else

NoConcatenate

fx:

load * Resident fx0;

Concatenate

load * Resident fx1;

Concatenate

load * Resident fx2;

Concatenate

load * Resident fx3;

drop Tables fx0,fx1,fx2,fx3;

endif

ElseIf

  scriptErrorCount >0

then

//********************* 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)"

)

;

NoConcatenate

fx1:

LOAD [asOfDate] AS [AsOf_Date],

[baseCurrency] AS [Currency],

[pivotCurrency] AS [Pivot_Currency],

[fxRate] AS [FX_Rate]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

let vFX1= len(trim(Peek('AsOf_Date',0,'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 vFX2= len(trim(Peek('AsOf_Date',0,'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 vFX3= len(trim(Peek('AsOf_Date',0,'fx3')));

if scriptErrorCount >1 or  $(vFX1)=0 or  $(vFX2)=0 or  $(vFX3)=0

then

LET ErrorMode=1;

exit Script;

  else

NoConcatenate

fx:

load * Resident fx0;

Concatenate

load * Resident fx1;

Concatenate

load * Resident fx2;

Concatenate

load * Resident fx3;

drop Tables fx0,fx1,fx2,fx3;

endif

elseif

$(vFX)=0

then

//********************* 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)"

)

;

NoConcatenate

fx1:

LOAD [asOfDate] AS [AsOf_Date],

[baseCurrency] AS [Currency],

[pivotCurrency] AS [Pivot_Currency],

[fxRate] AS [FX_Rate]

RESIDENT RestConnectorMasterTable;

DROP TABLE RestConnectorMasterTable;

let vFX1= len(trim(Peek('AsOf_Date',0,'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 vFX2= len(trim(Peek('AsOf_Date',0,'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 vFX3= len(trim(Peek('AsOf_Date',0,'fx3')));

if scriptErrorCount > 0 or  $(vFX1)=0 or  $(vFX2)=0 or  $(vFX3)=0

then

LET ErrorMode=1;

exit Script;

  else

NoConcatenate

fx:

load * Resident fx0;

Concatenate(fx)

load * Resident fx1;

Concatenate

load * Resident fx2;

Concatenate

load * Resident fx3;

drop Tables fx0,fx1,fx2,fx3;

endif

endif