Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi QV,
I have a SQL Query as :
Select SalesPerson, Count(Orders)
from Sales
where SalesPerson Like '%s'
groupby SalesPerson
having Count(Orders)<50
Now I would like to write same query in qlikview.Please help
Thanks in Advance
Ananth
Hi
If you mean you want the logic in Qlikview, then you will need two steps - first to get the data and the second to process it. If you want to do it in 1 step, then use your existing SQL query:
SQL Select SalesPerson, Count(Orders)
from Sales
where SalesPerson Like '%s'
group by SalesPerson
having Count(Orders)<50;
HTH
Jonathan
Temp:
Load SalesPerson,
Count(Orders) as Count
from Sales
where WildMatch( SalesPerson , '%s')
groupby SalesPerson ;
NoConcatenate
Final:
Load
*
Resident Temp where Count<50;
Drop Table Temp;
Try like below load script
tmp1:
Load
SalesPerson,
Orders
from Sales
where wildmatch( SalesPerson,'*s') ;
Noconcatenate
tmp2:
Load
SalesPerson,
Count(Orders) as COrders
from tmp1
groupby SalesPerson;
Drop table tmp1;
Noconcatenate
Final:
Load
SalesPerson,
COrders
from tmp2
Where COrders< 50;
Drop Table tmp2;
Thanks for your valuable time tresesco,
But, I want to do it in a single load, Is it possible
Hi
If you mean you want the logic in Qlikview, then you will need two steps - first to get the data and the second to process it. If you want to do it in 1 step, then use your existing SQL query:
SQL Select SalesPerson, Count(Orders)
from Sales
where SalesPerson Like '%s'
group by SalesPerson
having Count(Orders)<50;
HTH
Jonathan
As far as qlikview logic is concerned, no, it's not possible in one step. But yes, if you want to achieve in qlikview, same SQL query can be run using SQL Select. If you are fetching from a DB that supports SQL, you can use it directly.
LOAD SalesPerson,Orders;
SQL SELECT SalesPerson, Count(Orders) as Orders
from DATABASENAME.dbo.Sales
where SalesPerson Like '%s'
group by SalesPerson
having Count(Orders)<50;
Thanks Jonathan,
Its working fine....!
Thanks to one and all for your valuable responses.
Ananth
Yes in single load is not possible you can directly use the SQL load as jonathan suggest
SQL Select
SalesPerson,
Count(Orders) as Orders
from Sales
where SalesPerson Like '%s'
group by SalesPerson
having Count(Orders) < 50