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
