topic Re: sql to qlikview script in QlikView

sql to qlikview script

Mon, 19 Nov 2012 09:02:43 GMT

Hi,

I have a query like this:

select [<100k]=sum(d.[<100k]) ,

[100K]=isnull(sum((d.[100K])),''),

[100k>and<500k]=sum (d.[100k>and<500k]),

[500k]=sum(d.[500k]),

[500k>and<1M]=sum(d.[500k>and<1M]),

[1MIL>]= sum(d.[1MIL>])

from

(select [<100K]=(case when c.[Average Value] < (100000) then count(c.custcode) END),

[100K]=(case when c.[Average Value] = (100000) then count(c.custcode) END),

[100k>and<500k]=(case when c.[Average Value] > (100000) and c.[Average Value] < (500000)then count(c.custcode) END),

[500k]=(case when c.[Average Value] = (500000) then count(c.custcode) END),

[500k>and<1M]=(case when c.[Average Value] > (500000) and c.[Average Value] < (1000000)then count(c.custcode) END),

[1MIL>]=(case when c.[Average Value] > (1000000) then count(c.custcode) END)

from

(select a.custcode,b.cname,b.custtype,b.commtype,b.rate,b.status,

b.class,[Average Value]=avg(a.extension)

from tbl_tr..trin a

inner join tbl_sm b

ON a.custcode = b.custkey

where class='R'

and Status='A'

group by a.custcode,b.cname,b.custtype,b.commtype,b. rate,b.status,b.class)c

group by c.[Average Value])d

what is the equivalent script for this in qlikview or how will i do this in qlikview?

Thanks!

Re: sql to qlikview script

Gysbert_Wassenaar — Mon, 19 Nov 2012 09:14:02 GMT

Just use the sql statement in the load script as it is. Qlikview will send the sql statement to the dbms which will execute it and return the results to qlikview.

Table1:

select a,b,c from d where c='e';

Re: sql to qlikview script

Mon, 19 Nov 2012 09:18:24 GMT

Hi,

thanks for the quick reply, but the query i just posted is not a straight query. if you notice it contains query within a query

Re: sql to qlikview script

giakoum — Mon, 19 Nov 2012 09:22:24 GMT

A query within a query is not a problem. You could also use Interval Match however, if this suits you better.

Re: sql to qlikview script

Mon, 19 Nov 2012 21:31:19 GMT

i want this process done in qlikview that's why i want to know how i can rebuild this query in qlikview way

Re: sql to qlikview script

Tue, 20 Nov 2012 05:37:38 GMT

You could use Mapping or IntervalMatch to achieve your goal. Check this thread for details: http://community.qlik.com/thread/63420