Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
Thank you for taking the time to reply!
I am beginner with QlikView and I am trying to sort things out in my head.
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!
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'
...
Thank you all, for pointing me in the right direction.
This really helped me!
Thanks John,
This tip will help me a lot. Hugs.
Luiz Carlos