3 Replies Latest reply: May 21, 2014 4:18 PM by Jason Rathgeber RSS

    Are SubQueries possible in Qlikview Loadscripts?

    Roberto Postma

      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