topic Re: SQL Query in Qlikview in QlikView

SQL Query in Qlikview

Wed, 28 May 2014 11:59:24 GMT

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

Re: SQL Query in Qlikview

tresB — Wed, 28 May 2014 12:05:00 GMT

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;

Re: SQL Query in Qlikview

its_anandrjs — Wed, 28 May 2014 12:08:10 GMT

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;

Re: SQL Query in Qlikview

Wed, 28 May 2014 12:10:21 GMT

Thanks for your valuable time tresesco,

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

Re: SQL Query in Qlikview

jonathandienst — Wed, 28 May 2014 12:15:12 GMT

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

Re: SQL Query in Qlikview

tresB — Wed, 28 May 2014 12:20:58 GMT

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.

Re: SQL Query in Qlikview

Wed, 28 May 2014 12:23:25 GMT

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;

Re: SQL Query in Qlikview

Wed, 28 May 2014 12:26:43 GMT

Thanks Jonathan,

Its working fine....!

Re: SQL Query in Qlikview

Wed, 28 May 2014 12:30:55 GMT

Thanks to one and all for your valuable responses.

Ananth

Re: SQL Query in Qlikview

its_anandrjs — Wed, 28 May 2014 12:31:00 GMT

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