Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 !
Where do you see the error?
My bad !
+ 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'
+ I know have a synthetic key between the fx tables...
What a mess !
What is this saying...
May be use Alt(...., 0) for the definition to convert the null to 0
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
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...
I'd need to do that with all the fields of the table..
Why all the fields of the table?
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
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