Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Are SubQueries possible in Qlikview Loadscripts?

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
MonthrelationNamepaymentMethod
ProductName
Active
20123Customer1cashproduct1inactive
20123Customer1creditcardproduct2inactive
20123testJan2008klantcashproduct1inactive
20123Customer2cashproduct1active

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

Tags (1)
3 Replies
Not applicable

Re: Are SubQueries possible in Qlikview Loadscripts?

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...

erich_shiino
Honored Contributor

Are SubQueries possible in Qlikview Loadscripts?

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

Not applicable

Re: Are SubQueries possible in Qlikview Loadscripts?

This is a decent solution in my view. Thanks Jason