5 Replies Latest reply: Jul 10, 2017 2:49 PM by Martin Mahler RSS

    Nested Where Clause in Load Script-QlikSense

    Saima SM

      Hi all ,

       

      I have following code. Trying to filter a subset of values using nested where clause. What would be appropriate logic because I am getting error in script this way in second part of where cloause as "   ) expected. thanks

       

      HHMPM:

      Load *

      FROM [lib://ProgramQVD/HHMJOINPM.csv]

      (txt, utf8, embedded labels, delimiter is ',', msq)

      where (ManagerID ='' and Qtr='Q3' and Year = '2016/17')

      = (Load * FROM [lib://ProgramQVD/HHMJOINPM.csv]

      (txt, utf8, embedded labels, delimiter is ',', msq)

      where ManagerID <>'' and Qtr='Q4' and Year = '2016/17');

        • Re: Nested Where Clause in Load Script-QlikSense
          Dan Sullivan

          Try just using a join in the script:

           

          HHMPM:

          Load *

          FROM [lib://ProgramQVD/HHMJOINPM.csv]

          (txt, utf8, embedded labels, delimiter is ',', msq)

          where ManagerID ='' and Qtr='Q3' and Year = '2016/17';

           

           

          JOIN

           

           

          Load * FROM [lib://ProgramQVD/HHMJOINPM.csv]

          (txt, utf8, embedded labels, delimiter is ',', msq)

          where ManagerID <>'' and Qtr='Q4' and Year = '2016/17';

          • Re: Nested Where Clause in Load Script-QlikSense
            Dan Sullivan

            Okay, there might be a more elegant way but you can try this.  create a temp table, then select from the temp table and then drop the temp table.  If i misunderstood which one needs to happen first just flip flop the where clauses.

             

            HHMPM_TEMP:

             

            Load * FROM [lib://ProgramQVD/HHMJOINPM.csv]

            (txt, utf8, embedded labels, delimiter is ',', msq)

            where ManagerID <>'' and Qtr='Q4' and Year = '2016/17';

             

             

            HHMPM:

            Load *

            RESIDENT HHMPM_TEMP

            where ManagerID ='' and Qtr='Q3' and Year = '2016/17';

             

             

            DROP TABLE HHMPM_TEMP

              • Re: Nested Where Clause in Load Script-QlikSense
                Saima SM

                Hi, thanks but sorry it will not gonna work. I will try to explain the problem once again. you can suggest any solution.

                 

                I have One Table having record for Employees  in different quarters and years.

                Few Employees have ManagerID in Qtr 4 but not having ManagerID in Qtr3 (its mean this field is empty for those Emplyees in Q3)

                 

                I want to first filter those Employees with ManagerID in Q4(e.g emp11 emp33, emp77) . Then want to filter records for same employess (e.g emp11 emp33, emp77) from Qtr3. (Qtr3 may have many employees and not all are required only matching with filter as with ManagerID in Q4)

                the aim of this to review data for same employees when they have managerid in q4 vs when they didn't have in Qtr3. Hope I am able to explain the problem. thanks