topic Re: Union All in QV load script in QlikView

Union All in QV load script

Wed, 30 Sep 2009 05:42:20 GMT

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

johnw — Wed, 30 Sep 2009 05:52:27 GMT

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

Wed, 30 Sep 2009 07:01:57 GMT

Thank you for taking the time to reply!

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

First of all, can you tell me what's the differecne between LOAD and SELECT?
Is it possible to combine the load statement with:
- Left Join
- Group By
- Having
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

Anonymous — Wed, 30 Sep 2009 09:15:20 GMT

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.

Union All in QV load script

Wed, 30 Sep 2009 18:24:55 GMT

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

This really helped me!

Re: Union All in QV load script

Anonymous — Tue, 05 Apr 2016 13:08:09 GMT

Thanks John,

This tip will help me a lot. Hugs.

Luiz Carlos