4 Replies Latest reply: Sep 30, 2009 2:24 PM by vmar RSS

    Union All in QV load script

    vmar

      Hello all,

      I need to somehow create/simulate the following SQL query with QlikView.

       


      Select * From A
      Union All
      Select * From B


       

      Thanks in advance

        • Union All in QV load script
          John Witherspoon

          LOAD * FROM A;
          LOAD * FROM B;

          If the two loads have all the same fields, the two tables will be concatenated (union all) automatically. I prefer to be explicit, though, so I would do it like this, which forces concatenation even if some of the fields are different:

          MyTable:
          LOAD * FROM A;
          CONCATENATE (MyTable)
          LOAD * FROM B;

            • Union All in QV load script
              vmar

              Thank you for taking the time to reply!

              I am beginner with QlikView and I am trying to sort things out in my head.

              1. First of all, can you tell me what's the differecne between LOAD and SELECT?
              2. Is it possible to combine the load statement with:
                • Left Join
                • Group By
                • Having
              3. I also would like to apply some date-specific functions and to do SUM() on some of the fields.

              How do these things work out with the LOAD statement?

              The query I have in mind is something like:

               


              Select TT.DateField, TT.Month(DateField), Sum(TT.Amount), TT.Num/100, TT.GroupID, TT.AreaID, TT.ProductID, PT.ProductName
              From TransTable as TT
              Left Outer Join ProductTable as PT
              ON PT.ProductID = TT.ProductID AND TT.AreaID = PT.AreaID
              Group By AreaID, GroupID, ProductID
              Having AreaID = 'DAT', Year(DateField) = Year(GetDate)
              Union All
              ...


              All sorts of suggestions are very welocome.

              Thank you again!

                • Union All in QV load script
                  Michael Solomovich

                  Velislav,

                  1. SELECT - only from database. Within SELECT you can use the SQL syntax the same exactly way as outside of Qlikview.
                  LOAD - from file, from previously loaded logical table, "hardcoded" data, preceeding load. In LOAD, you can use Qlikview functions - and there are many.

                  2. Left Join - yes. It is different for LOAD. You create one table, and after that you can join another to it.
                  Group By - yes, both in LOAD and in SELECT
                  Having - only in SELECT.

                  3. There are many ways to do it. You can stay as much in SQL as you want, or leave the bare minimum of it. I'd probably use this:


                  LOAD
                  DateField as Date,
                  date(monthstart(DateField)) as Month,
                  sum(Amount) as Amount,
                  Num/100 as Num,
                  GroupID,
                  ProductID,
                  ProductName
                  WHERE year(DateField)=year(today())
                  GROUP BY DateField, Num, GroupID, ProductID, ProductName;
                  SQL SELECT
                  TT.DateField,
                  TT.Amount,
                  TT.Num,
                  TT.GroupID,
                  TT.AreaID,
                  TT.ProductID,
                  PT.ProductName
                  From TransTable as TT
                  Left Outer Join ProductTable as PT
                  ON PT.ProductID = TT.ProductID AND TT.AreaID = PT.AreaID
                  WHERE TT.AreaID='DAT'
                  ...

                  It maybe not exactly like this, depending on what you want to achieve.
                  Answers to all you questions and much more, you can find in QV documentaion and examples.