Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Union All in QV load script

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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;

View solution in original post

5 Replies
johnw
Champion III
Champion III

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;

Not applicable
Author

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!

Anonymous
Not applicable
Author

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.

Not applicable
Author

Thank you all, for pointing me in the right direction.

This really helped me!

Anonymous
Not applicable
Author

Thanks John,

This tip will help me a lot. Hugs.

Luiz Carlos