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.