Are SubQueries possible in Qlikview Loadscripts?

Mon, 26 Mar 2012 20:22:02 GMT

Hi there!

I need some advice… I need agood solution to select inactive donors for my customer?

Definitions

A donor is inactive when, for at least 12months there has been no payment in the factTable
Inactivity must be measured per month for each relation for each paymentmethod for each product name
so for example if there was only a payment on 15 january 2008 then all records must be INACTIVE except for the records with between january 2008 until december 2008

Datamodel

FactTable

idPaymentMethod
idProduct
idRelation
paymentAmount
paymentDate

Theresult must be like this… The table must have records for all months after January2006 and then a record for each relationname, each payment method eachproductname

Year	Month	relationName	paymentMethod	ProductName	Active
2012	3	Customer1	cash	product1	inactive
2012	3	Customer1	creditcard	product2	inactive
2012	3	testJan2008klant	cash	product1	inactive
2012	3	Customer2	cash	product1	active

In SQL Ihave a working query, which is this one


select 
min(YEAR(firstDayOfMonth))as dYear,
min(month(firstDayOfMonth))as dMonth,
min(r.name)as relationName,
min(bw.name)as paymentMethod,
MIN(p.name)as productName,
--here asubquery to determine (in)activity
--whenthere was a payment in the last 12 months, you're active, else inactive
case when(
selectCOUNT(id) From fact f 
                                   where 
                                   paymentDate>=
                                   (  
                                   DATEADD(month,-11, (     cast(min(YEAR(firstDayOfMonth))as varchar(40))   ) + '-'+ cast(  min(month(firstDayOfMonth)) asvarchar(40))+'-01')
                                   )          
                                   andpaymentDate <=
                                   DATEADD(DD,-1, DATEADD(M, 1,
                                   CAST(min(YEAR(firstDayOfMonth))AS VARCHAR(4)) + '/' +
            CAST(min(month(firstDayOfMonth)) ASVARCHAR(2)) + '/01'))
                                   and MIN(r.id)=f.idRelation --filter on relation  
                                   andMIN(bw.id) =f.idpaymentmethod--filter paymentmethod
) >0then 'Active' else 'Inactive' end as active
FROM
            MonthYear my
            full outer join fact f
            on 1=1 --on purpose a cartesianproduct: I want all combinations of customer, year and month
            left join relation r on r.id =f.idRelation
            left join product p on p.id =f.idproduct
            left join paymentmethod bw on bw.id= f.idpaymentmethod
group by
YEAR(my.firstDayOfMonth),
Month(my.firstDayOfMonth),
idrelation,
idproduct,
idpaymentmethod
order by
YEAR(my.firstDayOfMonth)desc,
Month(my.firstDayOfMonth)desc,
activedesc

Of course I can use this (working)sql statement in my load script…
But we’re working with QVD’s and(preferably) I don’t have access to the source-system
So I wonder how this can be achievedbest in Qlikview Scripts
What I actually need, is using asubquery in my loadscript to calculate active/inactive… Is that possible? If so…how?

In otherwords: can this SQL query also be run in Qlikview loadscripts (when loadingfrom QVD’s)?

I’d like to hear: hope my problem is declared well enough… If not, please let me knowhow I can make it more clear…?

sdfasdf

Re: Are SubQueries possible in Qlikview Loadscripts?

Sun, 01 Apr 2012 19:50:13 GMT

Hi there. I got no answers yet (I can imagine that my question was formulated too large...)

Lets retry:

Can I use a subquery in the Qlikview loadscripts, like it can be done in SQL in the way shown below?

select 
'Field 1' as field1,
(select COUNT(id) From factTable ) as field 2

I (preferably) don't use SQL as the customer has a bunch of QVD files that we have to use as source...

(the reason for asking: I have a nice working SQL question with a subquery, and hope it can be done in the Qlikview script as well)

Any help is appreciated (even when perhaps the question is not clear enough...

Are SubQueries possible in Qlikview Loadscripts?

erichshiino — Sun, 01 Apr 2012 20:43:09 GMT

Hi, Roberto.

It's not possible to use subqueries like this.

You can run it directly on the database, but as you said in your case you already have qvds.

You can use aggregation expressions and joins to recreate your original query.

For exampe:

Users:

load user_id

from...

tmpAccess:

Load user_id, count( id_access) as qtty

from. ...

left join( Users)

Load * from tmpAccess;

drop table tmpAccess ;

Hope this helps,

Erich

Re: Are SubQueries possible in Qlikview Loadscripts?

Wed, 21 May 2014 20:18:33 GMT

This is a decent solution in my view. Thanks Jason

topic Are SubQueries possible in Qlikview Loadscripts? in QlikView

Are SubQueries possible in Qlikview Loadscripts?

Re: Are SubQueries possible in Qlikview Loadscripts?

Are SubQueries possible in Qlikview Loadscripts?

Re: Are SubQueries possible in Qlikview Loadscripts?