In a data load, how to count the number of instances of a specific type within a limited time period?

[mcorser]

Hi,

I've loaded a set of fishing license customer data that includes customers (CustomerId) with many purchases (TransDate) for two different products ("Annual" and "ShortTerm").

I'd like to add a field in the data load (Prev365ShortTerm) that, for each "Annual" purchase, displays the total number of "ShortTerm" purchases by that particular customer within the last 365 days. If it is a ShortTerm purchase, return Null.

CustomerId	TransDate	LicenseType	Prev365ShortTerm
252	4/20/2013	Annual	0
252	4/9/2014	ShortTerm	Null
252	3/21/2015	Annual	1
253	7/18/2019	ShortTerm	Null
254	7/19/2017	Annual	0
254	8/2/2018	Annual	0
254	7/2/2020	ShortTerm	Null
254	9/24/2020	ShortTerm	Null
254	4/4/2021	Annual	2

Can I do this in the Data Load Editor, or is it best done as an expression? Any ideas for the formulas?

Thanks,

Mark

[rwunderlich]

A clarification. Are you looking back at exactly a 365 day window or the full window between Annual purchases?  If a 365 day window, than your customer 254 example doesn't make sense. Or I'm misunderstanding. 

-Rob

[mcorser]

Hi,

Apologies. I fixed the example. It should have been 2020, not 2019!

[rwunderlich]

One approach would be:

Data:
LOAD CustomerId,
     TransDate,
     LicenseType
FROM
[https://community.qlik.com/t5/App-Development/In-a-data-load-how-to-count-the-number-of-instances-of...]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

PurchaseTemp:
LOAD
  *,
  Date(TransDate - IterNo()) as WindowDate
While IterNo() < 365
;
LOAD
  CustomerId,
  TransDate,
  LicenseType
Resident Data
Where LicenseType = 'Annual'
;
Join (PurchaseTemp)
Load
CustomerId,
     TransDate as WindowDate,
     1 as Purchase
Resident Data
Where LicenseType = 'ShortTerm'
; 
Left Join (Data)
LOAD
  CustomerId,
  TransDate,
  LicenseType,
  Sum(Purchase) as Prev365ShortTerm
Resident PurchaseTemp
Group By CustomerId,
TransDate,
LicenseType
;

Drop Table PurchaseTemp;  

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com