10 Replies Latest reply: Mar 18, 2015 7:26 AM by Giovanni Scalzo RSS

    Multi If Statements

    Giovanni Scalzo

      I have the following expression:

       

      if

          (FVCON_ID_CONTAT=5,

                  if(

                      (date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') =

                      date#(trim(right(Date(FVCON_TS_ESTR,'YYYYMMDD'),2) & '/' & mid(Date(FVCON_TS_ESTR,'YYYYMMDD'),5,2) & '/' & Left(Date(FVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD')

                           or (FVCON_ID_CONTAT=5 and FVCON_DATA_FI=0)    

                      ),'aperte','chiuse' ), 'chiuse' ) as SelettorePraticheAperteChiuse,   

       

      But it's incorrect.

      If I check the data obtained are also different from the data provided prefixed FVCON_ID_CONTAT = 5.
      So i think that the condition FVCON_ID_CONTAT = 5 is not checked.

      How can I risolver the problem?

        • Re: Multi If Statements
          Jyothish KC

          Hi Giovanni,

           

          For nested if use this syntax:

           

          if(Condition, do this,if(condition, do this,else do this))

           

          Regards

          KC

          • Re: Multi If Statements
            Harshal Patil

            Hi

            try this,

             

            if(FVCON_ID_CONTAT=5, 'chiuse',

            if( (date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') =

            date#(trim(right(Date(FVCON_TS_ESTR,'YYYYMMDD'),2) & '/' & mid(Date(FVCON_TS_ESTR,'YYYYMMDD'),5,2) & '/' &

            Left(DFVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD') or (FVCON_ID_CONTAT=5 and FVCON_DATA_FI=0)),'aperte','chiuse')) as SelettorePraticheAperteChiuse,

            • Re: Multi If Statements
              Robert Mika

              Attached your data or file

                • Re: Multi If Statements
                  Giovanni Scalzo

                  SET ThousandSep='.';

                  SET DecimalSep=',';

                  SET MoneyThousandSep='.';

                  SET MoneyDecimalSep=',';

                  SET MoneyFormat='€ #.##0,00;-€ #.##0,00';

                  SET TimeFormat='hh:mm:ss';

                  SET DateFormat='DD/MM/YYYY';

                  SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

                  SET MonthNames='gen;feb;mar;apr;mag;giu;lug;ago;set;ott;nov;dic';

                  SET DayNames='lun;mar;mer;gio;ven;sab;dom';

                  SET NullValue='<mancante>';

                   

                   

                  // ODBC CONNECT TO [AS 400 UAT FINPIEMONTE] (XUserId is MFcAGDVIfKVCEKBK, XPassword is BcYTODVIfKVCEKFE);

                   

                  ODBC CONNECT TO [XXXXXXXXXXXXXXX] (XUserId is YYYYYYYYYYYYY, XPassword is ZZZZZZZZZZZZ);

                   

                   

                  set vPath='C:\ProgramData\QlikTech\Documents\QVD_PROD\';

                  set vDatiOn='no';

                  set vReportOn='sì';

                   

                   

                  SelettorePraticheAperteChiuse:

                  LOAD     

                      upper(FVCON_IDFONDO & '-' &

                           FVCON_NUM_DOM_AS400 & '-' &

                          FVCON_TIPOL_AGEVOL & '-' &

                           FVCON_PROGR_WF & '-' &

                           FVCON_PROGR_TRANCHE) as ChiaveID,

                          date (date#(FVCON_DATA_FI,'YYYYMMDD'),'DD-MM-YYYY')     as [DataFine],

                  if

                      (FVCON_ID_CONTAT=5,

                              if(

                                  (date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') =

                                  date#(trim(right(Date(FVCON_TS_ESTR,'YYYYMMDD'),2) & '/' & mid(Date(FVCON_TS_ESTR,'YYYYMMDD'),5,2) & '/' & Left(Date(FVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD')

                                       or (FVCON_ID_CONTAT=5 and FVCON_DATA_FI=0)    

                                  ),'aperte','chiuse'

                                ),'chiuse') as SelettorePraticheAperteChiuse,       

                                    

                          if(

                                   (FVCON_ID_CONTAT=5 and

                                       date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') <>

                                      date#(trim(right(Date(FVCON_TS_ESTR,'YYYYMMDD'),2) & '/' & mid(Date(FVCON_TS_ESTR,'YYYYMMDD'),5,2) & '/' & Left(Date(FVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD') 

                                  )

                              ,date(right(trim(FVCON_DATA_FI),2) & '/' & mid(trim(FVCON_DATA_FI),5,2) & '/' & left(trim(FVCON_DATA_FI),4))) as PraticheChiuseDataFine,

                           if(

                                   (FVCON_ID_CONTAT=5 and

                                       date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') <>

                                      date#(trim(right(Date(FVCON_TS_ESTR,'YYYYMMDD'),2) & '/' & mid(Date(FVCON_TS_ESTR,'YYYYMMDD'),5,2) & '/' & Left(Date(FVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD') 

                                  )

                              ,left(trim(FVCON_DATA_FI),4)) as PraticheChiuseAnnoFine,

                           if(

                                   (FVCON_ID_CONTAT=5 and

                                       date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') <>

                                      date#(trim(right(Date(FVCON_TS_ESTR,'YYYYMMDD'),2) & '/' & mid(Date(FVCON_TS_ESTR,'YYYYMMDD'),5,2) & '/' & Left(Date(FVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD') 

                                  )

                              ,month(date(right(trim(FVCON_DATA_FI),2) & '/' & mid(trim(FVCON_DATA_FI),5,2) & '/' & left(trim(FVCON_DATA_FI),4)))) as PraticheChiuseMeseFine,

                          

                          

                           if(

                                   (FVCON_ID_CONTAT=5 and

                                       date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') <>

                                      date#(trim(right(Date(FVCON_TS_ESTR,'YYYYMMDD'),2) & '/' & mid(Date(FVCON_TS_ESTR,'YYYYMMDD'),5,2) & '/' & Left(Date(FVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD') 

                                  )

                              ,day(date(right(trim(FVCON_DATA_FI),2) & '/' & mid(trim(FVCON_DATA_FI),5,2) & '/' & left(trim(FVCON_DATA_FI),4)))) as PraticheChiuseGiornoFine,

                             

                             

                           if(FVCON_ID_CONTAT=5 and FVCON_GIORNI=0,'valori a zero','altri valori') as SelettoreValoriAZero

                • Re: Multi If Statements
                  Prashant Sangle

                  Hi,

                   

                  try below

                   

                  if(FVCON_ID_CONTAT=5,

                        if(

                                 num(date#(FVCON_DATA_FI,'YYYYMMDD')) = Num(Date#(FVCON_TS_ESTR,'YYYYMMDD')) AND

                                      FVCON_DATA_FI=0,'aperte','chiuse'

                          ),

                  'chiuse' ) as SelettorePraticheAperteChiuse

                   

                  Dont play too much with Date() and Date#(), It make confusion. Keep it as simple as you can

                  and one more advice always better if you compare number instead of date

                   

                  Regards

                  • Re: Multi If Statements
                    Henric Cronström

                    It would be easier to debug if you use a preceding Load to do this. Like

                     

                    Load *,

                         If( .... ) as SelettorePraticheAperteChiuse;

                    Load

                         date#(trim(right((FVCON_DATA_FI),2) & '/' & mid((FVCON_DATA_FI),5,2) & '/' & Left((FVCON_DATA_FI),4)),'YYYYMMDD') as FVCON_Date1,

                         date#(trim(right(Date(FVCON_TS_ESTR,'YYYYMMDD'),2) & '/' & mid(Date(FVCON_TS_ESTR,'YYYYMMDD'),5,2) & '/' & Left(Date(FVCON_TS_ESTR,'YYYYMMDD'),4)),'YYYYMMDD') as FVCON_Date2

                     

                    and then use the FVCON_Date1 and FVCON_Date2 in your If(...) function.

                     

                    See more on Preceding Load

                     

                    HIC

                    • Re: Multi If Statements
                      Rupert Cavendish

                      I think that you are getting confused with date syntax .....

                       

                      In principle you are using the correct method of your IF .... statements but I would redo the part that checks the month ...

                       

                      num(month(FVCON_TS_ESTR)) .... will give you the number for the month and this can be used to compare against you field coming in .... You can use a similar approach to the days and years. This will give you the exact numbering that you want when comparing to your FVCON_DATA_FI field.

                       

                      Hope this helps