4 Replies Latest reply: Oct 27, 2010 4:01 AM by JurajChovan RSS

    How can to add AND/OR in IF statement?

      hi friends,

      for more detail I'm trying describe my problem (because I didn't find any solution).
      I have following data (in Excel datasource):
      error loading image
      on QlikView sheet lies ListBox object "Months" (with loaded field "MyMonthName") and Chart object "test", with dimension defined like:
      =if(MyMonthStart=MyMonthName,MyProjectName,'XXX')
      and two expressions - "project start" (field "MyProjectStartDate") and "project end" (field "MyProjectEndDate").
      error loading image
      Now I would like expand dimension's condition to something similar like:
      if (MyMonthStart<MyMonthName AND MyMonthEnd=MyMonthName) OR (MyMonthStart=MyMonthName) then MyProjectName
      else
      end

      How can write that condidtion (AND and OR in IF statement)?

        • How can to add AND/OR in IF statement?

          For more details:

          eg. if I select value "February" on object "months", chart "test" would displayed following projects:

          project 08 started at: 1.2.2010 ended: 15.5.2010
          and also:
          project 02 started at: 1.1.2010 ended: 1.2.2010
          project 03 started at: 1.1.2010 ended: 28.2.2010
          project 06 started at: 31.1.2010 ended: 1.2.2010
          project 11 started at: 1.12.2009 ended: 1.2.2010

           

          • How can to add AND/OR in IF statement?
            Miguel Angel Baeyens de Arce

             


            JurajChovan wrote:if (MyMonthStart<MyMonthName AND MyMonthEnd=MyMonthName) OR (MyMonthStart=MyMonthName) then MyProjectName
            else
            end

            How can write that condidtion (AND and OR in IF statement)?


            Hello,

            Try something like

             

            if((MyMonthStart < MyMonthName AND MyMonthEnd=MyMonthName) OR (MyMonthStart=MyMonthName), MyProjectName, 'XXX')


            For performance issues though I would check that in the load script.

            Hope that helps.

             

              • How can to add AND/OR in IF statement?

                very thanks, it's working.

                  • How can to add AND/OR in IF statement?

                    there is complete solution:

                    data load from datasource via following scripts:

                    Month:
                    LOAD [MyMonthID],
                    [MyMonthName]
                    FROM
                    [C:\...\DataSourceFile.xls]
                    (biff, embedded labels, table is Month$);

                    Years:
                    LOAD * INLINE [
                    MyYear
                    2008
                    2009
                    2010
                    2011
                    ];

                    LOAD MyProjectName,
                    MyProjectStartDate,
                    MyProjectEndDate,
                    MyProjectTotal,
                    Month(MyProjectStartDate) AS MyMonthStart,
                    Year(MyProjectStartDate) AS MyYearStart,
                    Month(MyProjectEndDate) AS MyMonthEnd,
                    Year(MyProjectEndDate) AS MyYearEnd
                    FROM
                    [C:\...\DataSourceFile.xls]
                    (biff, embedded labels, table is TestovacieData$);

                    and on QlikView sheet are ListBox with field "MyYear" (for selecting year), ListBox with field "MyMonthName" (for selecting month) and Chart object with expressions: MyProjectName, MyProjectStartDate, MyProjectEndDate, MyProjectTotal and with dimension defined like:

                    =if(num(month(date#(MyMonthName,'MMM')))=12,if((MyProjectStartDate<=MakeDate(MyYear,num(month(date#(MyMonthName,'MMM')))) AND MyProjectEndDate>=MakeDate(MyYear+1,num(month(date#(MyMonthName,'MMM')))-11))
                    OR (MyProjectStartDate>=MakeDate(MyYear,num(month(date#(MyMonthName,'MMM')))) AND MyProjectStartDate<MakeDate(MyYear+1,num(month(date#(MyMonthName,'MMM')))-11))
                    OR (MyProjectEndDate>=MakeDate(MyYear,num(month(date#(MyMonthName,'MMM')))) AND MyProjectEndDate<MakeDate(MyYear+1,num(month(date#(MyMonthName,'MMM')))-11))
                    ,MyProjectName,'XXX'),
                    if((MyProjectStartDate<=MakeDate(MyYear,num(month(date#(MyMonthName,'MMM')))) AND MyProjectEndDate>=MakeDate(MyYear,num(month(date#(MyMonthName,'MMM')))+1))
                    OR (MyProjectStartDate>=MakeDate(MyYear,num(month(date#(MyMonthName,'MMM')))) AND MyProjectStartDate<MakeDate(MyYear,num(month(date#(MyMonthName,'MMM')))+1))
                    OR (MyProjectEndDate>=MakeDate(MyYear,num(month(date#(MyMonthName,'MMM')))) AND MyProjectEndDate<MakeDate(MyYear,num(month(date#(MyMonthName,'MMM')))+1))
                    ,MyProjectName,'XXX'))

                    little bit complicated, but works.