2 Replies Latest reply: Aug 31, 2012 1:02 AM by jagan mohan rao appala RSS

    Where clause to filter multiple QVDs that inner join

      Hello everyone,

       

      I am getting frustrated with a recurring error that I am trying to resolve.  Basically, I have about 6 QVDs that I am inner joining but want to filter (with where clauses) a combination of criteria across the QVDs.  I get a "field not found" error because I believe Qlikview is assuming I mean to put where restrictions on only the last QVD.  Here an example of what I wrote:

       

      TABLE1:

       

      LOAD

             Number,

             Quote,

             ID

      FROM [QVD A];

       

      Inner join

       

      LOAD

            ID,

            Name as Cat_Name,

            Sale

      FROM [QVD B];

       

      Inner join

       

      LOAD

            Sale,

            Approvals,

            Primary

      FROM [QVD C];

       

      Inner join

       

      LOAD

            Approvals,

            User

      FROM [QVD D]

       

            Where (Cat_Name = 'Example'

            OR

            Number like '6657*')

             AND Primary = 'true';

       

      Upon doing this, I would get a Error: "Field not found: <Cat_Name>".  I believe this is because qlikview is assuming my where clause only meant for the last QVD that I'm loading ([QVD D]).

       

      Does anyone have suggestions on how to properly write this to where I can filter across fields from multiple QvDs that I am loading and joining?  I am thinking about this in a SQL script mindset (for better or worse) in which I define the table fields, define the FROM (and the joins) and then write the where clauses after all the joins are written.  Help is appreciated!

        • Re: Where clause to filter multiple QVDs that inner join

          Hi,

           

          Your script have some syntax errors. I noticed that you followed a SQL type syntax but QV script is a little bit different. You need to add conditions on the qvd when its loaded not at the end. If there are any conditions with more than one table then you load all and filter with a resident load.

           

          You can try the below script:

           

          TABLE1:
          LOAD Number,
               Quote,
               ID
          FROM [QVD A];
          Inner join
          LOAD ID,
               Name as Cat_Name,
               Sale
          FROM [QVD B];
          Inner join 
          LOAD Sale,
               Approvals,
               Primary
          FROM [QVD C];
          Inner join 
          LOAD Approvals,
               User
          FROM [QVD D];
          
          
          NewTable:
          LOAD Number,
               Quote,
               ID,
               Name as Cat_Name,
               Sale,
               Approvals,
               Primary,
               User
          Resident Table1
          Where (Name = 'Example' OR Number like '6657*') and Primary = 'true';
          
          Drop Table Table1;
          

           

          Cheers,

          Kiran Rokkam.

          • Re: Where clause to filter multiple QVDs that inner join
            jagan mohan rao appala

            Hi,

             

            Try this script

             

            Temp:

            LOAD

                   Number,

                   Quote,

                   ID

            FROM [QVD A];

            Inner join

            LOAD

                  ID,

                  Name as Cat_Name,

                  Sale

            FROM [QVD B];

             

            Table1:

            LOAD

                  Sale,

                  Approvals,

                  Primary

            FROM [QVD C]

            WHERE Primary='true;

            INNER JOIN

            LOAD

                   Number,

                   Quote,

                   ID,

                   Cat_Name,

                   Sale

            RESIDENT TABLE1

            Where (Cat_Name = 'Example'

                  OR

                  Number like '6657*');

            Inner join

            LOAD

                  Approvals,

                  User

            FROM [QVD D];

             

            DROP TABLE Temp;

             

             

            Hope this helps you.

             

            Regards,

            Jagan.