I need some advice… I need agood solution to select inactive donors for my customer?
- 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
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
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…?