10 Replies Latest reply: Mar 1, 2018 11:27 AM by Sunny Talwar RSS

    Need HELP with if else logic !!

    omar bensalem

      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 !

        • Re: Need HELP with if else logic !!
          Sunny Talwar

          Where do you see the error?

            • Re: Need HELP with if else logic !!
              omar bensalem

              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 !

                • Re: Need HELP with if else logic !!
                  Sunny Talwar

                  What is this saying...

                   

                  Capture.PNG

                  • Re: Need HELP with if else logic !!
                    Sunny Talwar

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

                      • Re: Need HELP with if else logic !!
                        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

                          • Re: Need HELP with if else logic !!
                            omar bensalem

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

                              • Re: Need HELP with if else logic !!
                                Sunny Talwar

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

                                Why all the fields of the table?

                                  • Re: Need HELP with if else logic !!
                                    omar bensalem

                                    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

                                      • Re: Need HELP with if else logic !!
                                        omar bensalem

                                        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

                                        • Re: Need HELP with if else logic !!
                                          Sunny Talwar

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