6 Replies Latest reply: Jun 13, 2016 10:07 AM by florentina dogaru RSS

    Where Clause

      Hi. I recently updated my script to read from a data source instead of an Excel file.  When it was reading the excel data, my where clause worked.  Once I added it to the script of my data source and changed the table name, it no longer works.  Here is my Where clause, please provide advise.

       

       

      AND (dATE(AL7.METER_DATE) < monthstart(today()))

       

      If I comment this line out, everything still works wonderfully.  When I add it back in and reload - it has errors.

       

      Thanks!

        • Re: Where Clause
          Onno van Knotsenburg

          You sure the field is called "AL7.METER_DATE" and not just "METER_DATE"?

          • Re: Where Clause
            Sunny Talwar

            Can you share the complete script?

              • Re: Where Clause

                 

                LOAD
                UNIT_NO as "UNIT #",
                UNIT_ID,
                STATUS,
                YEAR,
                MAKE,
                MODEL,
                "Desc 1" as "UNIT DESCRIPTION",
                "Desc 2" as "ALT UNIT DESCRIPTION",
                SERIAL_NO as "VIN/SERIAL #",
                CATEGORY,
                "Desc 3" as "CATEGORY DESCRIPTION",
                CATEGORY_CLASS as "ASSET CLASS",
                "Desc 4" as "ASSET CLASS DESCRIPTION",
                "Label 1" as "USING DEPT CO CODE",
                "Dept No 1" as "USING DEPT COST CENTER",
                "Desc 5" as "USING DEPT DESCRIPTION",
                "Name 1" as "PRKG LOC DESCRIPTION",
                "Name 2" as "MAINT LOC DESCRIPTION",
                "Loc 1" as "PRKG LOC",
                "Loc 2" as "MAINT LOC",
                "Desc 6" as "OWNER",
                OWNER_TYPE as "OWNER TYPE",
                ACT_CODE as "ACTIVITY TYPE",
                METER as "ODOMETER READING",
                Floor(METER_DATE) as "ODOMETER READING DATE",
                CLASS2 as "TYPE OF USE",
                OPERATOR as "ASSIGNED EMPLOYEE SAP #",
                OPERATOR_NAME as "ASSIGNED EMPLOYEE",
                NON_STD_FL as "SPECIALTY VEHICLE",
                MRF as "MAINT REPAIR FACTOR",
                "Label 2" as "OWNING DEPT CO CODE",
                "Dept No 2" as "OWNING DEPT COST CENTER",
                "Desc 7" as "OWNING DEPT DESCRIPTION",
                MCC,
                "Desc 8" as "MCC DESCRIPTION",
                Date(Floor(IN_SERV_DT)) as "IN SERVICE DATE",
                Num#(Date(AddMonths(Today(),-1),'YYYYMM')) as [UNIT INFO READ DATE];

                SQL
                SELECT AL7.UNIT_NO,
                AL7.UNIT_ID,
                AL7.STATUS,
                AL7.YEAR,
                AL7.MAKE,
                AL7.MODEL,
                AL5.DESCRIPTION as "Desc 1",
                AL7.DESCRIPTION as "Desc 2",
                AL7.SERIAL_NO,
                AL9.CATEGORY,
                AL9.DESCRIPTION as "Desc 3",
                AL9.CATEGORY_CLASS,
                AL8.DESCRIPTION as "Desc 4",
                AL4.LABEL as "Label 1",
                AL2.DEPT_NO as "Dept No 1",
                AL2.DESCRIPTION as "Desc 5",
                AL1.NAME as "Name 1",
                AL3.NAME as "Name 2",
                AL1.LOCATION as "Loc 1",
                AL3.LOCATION as "Loc 2",
                AL6.DESCRIPTION as "Desc 6",
                AL7.OWNER_TYPE,
                AL7.ACT_CODE,
                AL7.METER,
                AL7.METER_DATE,
                AL7.CLASS2,
                AL7.OPERATOR,
                AL7.OPERATOR_NAME,
                AL9.NON_STD_FL,
                AL9.MRF,
                AL11.LABEL as "Label 2",
                AL10.DEPT_NO as "Dept No 2",
                AL10.DESCRIPTION as "Desc 7",
                AL7.MCC,
                AL12.DESCRIPTION as "Desc 8",
                AL7.IN_SERV_DT
                FROM MFIVE.LOC_GEN AL1,
                MFIVE.DEPT_MAIN AL2,
                MFIVE.LOC_GEN AL3,
                MFIVE.ORGANIZATION AL4,
                MFIVE.TECH_SPEC AL5,
                MFIVE.CLASS3 AL6,
                MFIVE.UNIT_MAIN AL7,
                MFIVE.CATEGORY_CLASS AL8,
                MFIVE.CATEGORY AL9,
                MFIVE.DEPT_MAIN AL10,
                MFIVE.ORGANIZATION AL11,
                MFIVE.MCC AL12
                WHERE ( AL7.CLASS3 = AL6.CLASS3
                AND AL7.USING_DEPT = AL2.DEPT_ID
                AND AL7.MAINT_LOC = AL3.LOCATION
                AND AL7.PARKING_LOC = AL1.LOCATION
                AND AL7.SPEC_NO = AL5.SPEC_NO AND 
                AL7.OWNER_DEPT = AL10.DEPT_ID AND 
                AL7.MCC = AL12.MCC
                AND AL2.DEPT_ID=AL4.DEPT_ID
                AND AL5.CATEGORY=AL9.CATEGORY
                AND AL9.CATEGORY_CLASS=AL8.CATEGORY_CLASS AND AL11.DEPT_ID=AL10.DEPT_ID) 
                AND ((AL7.STATUS IN ('A')
                AND AL4.LEVEL_NO=2
                AND AL11.LEVEL_NO=2))
                AND (AL9.CATEGORY_CLASS= '1' OR AL9.CATEGORY_CLASS = '2' OR AL9.CATEGORY_CLASS = '3' OR AL9.CATEGORY_CLASS = '4' OR AL9.CATEGORY_CLASS = '5' OR AL9.CATEGORY_CLASS = '6')
                // AND (dATE((Floor(METER_DATE)) < monthstart(today()))

              • Re: Where Clause

                I went back and checked and it is listed as Floor(METER_DATE)  so I tried that, and I tried just METER_DATE.  It still returns an error, and it says it has a missing expression.

                  • Re: Where Clause
                    Sunny Talwar

                    You are using the where statement within the SQL statement and the QlikView functionality doesn't work in SQL statements. You would need to use functions which are available with your SQL. Another alternative is to do this within the LOAD statement, but I would encourage you to figure out a way to do this within the SQL (like you are doing right now), but using a SQL syntax (and not QlikView functions such as MonthStart)