SQL Query in Qlikview

[#1]

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

[tresesco]

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;

[its_anandrjs]

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;

[#4]

Thanks for your valuable time tresesco,

But, I want to do it in a single load, Is it possible

[jonathandienst · Accepted solution]

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

[tresesco]

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.

[#7]

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;

[#8]

Thanks Jonathan,

Its working fine....!

[#9]

Thanks to one and all for your valuable responses.

Ananth

[its_anandrjs]

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