12 Replies Latest reply: Apr 3, 2017 9:43 AM by Gautam Valecha RSS

    SQL statements load script editor(Qlik sense)

    Gautam Valecha

      Hello, Can someone help me out with understanding, how does one mimic a SQL where clause in the QLIK sense load script editor ?

      Also, I am trying to use a sql query to pull some data from multiple tables using left joins followed by a where clause to capture dates in a dynamic range of 1 month going back from today. Any help is appreciated.

        • Re: SQL statements load script editor(Qlik sense)
          omar bensalem

          sthing like this:

           

          load

          a,

          b,

          c;

          select a,

          b,

          c

          from souce where a=1;

           

          or

          load

          a,

          b,

          c where a=1 ;

          select a,

          b,

          c

          from souce ;

           

          As for left join, as you  probably know, Qlik associate table by column name, if we have table 1 with colum A, B and C and table 2 with columns A , Y and X, Qlik will consider column A as a mapping key.

           

          Now suppose we have:

           

          table 1:

          A,

          B,

          C from source1;

           

          table2:

          A,

          X,

          Y from source 2;

           

          We want to left join table 2 to table 1(table 1 is principle table).

          Here's how we proceed

           

           

          table 1:

          A,

          B,

          C from source1;

          left join(table1)

          table2:

          A,

          X,

          Y from source 2;

           

          the outcome will be table1 containing ALL columns A,B , C and the joined X AND Y from table 2.

           

          Suppose that we want to only keep columns where A=1?

           

          This is how we alter our script:

          table 1:

           

          load *

          where A=1;

           

          A,

          B,

          C from source1;

          left join(table1)

          table2:

          A,

          X,

          Y from source 2;

           

          I hope that was clear enough?

          • Re: SQL statements load script editor(Qlik sense)
            Gautam Valecha

            Well that was definitely helpful. Thank you.

            But to go one step further, how can I write the following statement in qlik ?

             

            SELECT o.*

            FROM source.table1  o

            where o.date_id >= cast(getdate() - 31 as date)

            and o.date_id <= cast(getdate() - 1 as date)

            • Re: SQL statements load script editor(Qlik sense)
              omar bensalem

              You can write it as it is.

               

              The good thing with Qlik is that you can use its functionalities in the load statement, but also, you can use what your were using within the select statement.

               

              In your case you can do as follow:

               

              LOAD *;

               

              SELECT o.*

              FROM source.table1  o

              where o.date_id >= cast(getdate() - 31 as date)

              and o.date_id <= cast(getdate() - 1 as date)

               

               

              With this, you did the manipulations within the select part, then load * to load all your columns from your source after the made transformations.

                • Re: SQL statements load script editor(Qlik sense)
                  Gautam Valecha

                  Omar, thanks a ton. It all worked. I am assuming all SQL queries will work as it is.

                  I have a another question digressing from the load script editor. It's regarding the regular script editor.

                  I have a dataset where users are a dimension and the customer they perform a specific task for is another dimension.

                  eg:

                  Let's say the table represents one task only;

                   

                  User   Customer    Counter(Always 1)

                  A           Cus A.          1

                  B           Cus A.          1

                  C           Cus B.          1

                  C           Cus C.         1

                  A           Cus D.         1

                  B           Cus E.         1

                   

                  I want to create a table such that, upon selection of a specific user, the table displays the customers the user worked on. Along with these it also shows the other users that touched these customers and other unique customers that were touched by these new users.

                   

                  For example for I were to select User A from above:

                   

                  Customer/User     A     B

                  Cus A.                 1      1

                  Cus D.                 1      0

                  Cus E.                 0      1

                   

                   

                  I hope I was clear enough in trying to explain what I'm looking for. Let me know if you have any suggestions or if I can help explain the above better. Thanks.