Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

alextomlins
New Contributor II

Please help :( Identifying multiple subscriptions from customer events table

Hi all,

I've posted (poorly) about this question already and so i've decided the best way to go about it is to show you what table i have and what i want to transform it to.

The table I have is an events table

LicenseHistoryTable:

CustomerID

//(e.g 1, unique to every customer but NOT subscription)

,EngagementType

//(e.g. 'Subscribed' or 'Cancelled', Customer can have multiple subscriptions and have multiple subscribes and Cancellations in tat //Customer ID)

,LoggedDateTimeUtc

//(Date Time format, give you date and time of event)

CustomerIDEngagementTypeLoggedDateTimeUtc
1Subscribe

2018-08-01 11:46:08.210

1Cancellation2018-08-05 11:46:08.210
1Subscribe2018-08-10 11:46:08.210
1Cancellation2018-08-15 11:46:08.210
2Subscribe2018-09-01 11:46:08.210
2Cancellation2018-09-10 11:46:08.210
2Subscribe2018-09-12 11:46:08.210

So the reason i can't just do min(susbcribe) and max(cancellation) in EngagementType field is because if a user has 'subscribe - cancel - subscribe - cancel' scenario we would count that as one subscription but we want to count that as two subscriptions. The table i would like to get to is as follows;

Subscriptions Table:

SubscriptionID

//(INT - Detailing a unique key for every 'subscribe - cancel' pairing or just subscribe if already after a cancellation)

,CustomerID

//(INT - as in the tale previously linking to each customer)

,SubscriptionNo

//This will detail if this is the first subscription, second subscription and so per CustomerID)

StartDateTime:

//(DateTime - This will be the start Date Time for that SubscriptionNo for the relevant CustomerID)

,EndDateTime

//(DateTime- This will be either the current Date if the customer has not cancelled in this subscription or the Cancellation for that //subscription)

Active

//(boolean - this will detail whether that SubscriptionNo has a cancellation i.e Is that customer still live)

SubscriptionIDCustomerIDSubscriptionNoStartDateTimeEndDateTime

Active

1112018-08-01 11:46:08.2102018-08-05 11:46:08.2100
2122018-08-10 11:46:08.2102018-08-15 11:46:08.2100
3212018-09-01 11:46:08.2102018-09-10 11:46:08.2100
3222018-09-12 11:46:08.210getdate()1
1 Solution

Accepted Solutions
felipedl
Valued Contributor III

Re: Please help :( Identifying multiple subscriptions from customer events table

HI Alex,

Im pasting the code here and attaching the QVW i used to get what you need:

CustomerTable:

LOAD CustomerID,

     EngagementType,

     // Transforms the date you have into a numeric date

     Date#(subfield(LoggedDateTimeUtc,'.',1),'YYYY-MM-DD hh:mm:ss') as LoggedDateTimeUtc

FROM

(ooxml, embedded labels, table is Plan1);

NoConcatenate

Ordered:

Load

*,

// id so i can create key for getting the end date to row directly above the current one

RowNo() as Id,

if (EngagementType='Subscribe',1,0) as [Flag Subscription]

Resident CustomerTable

order by

CustomerID,

LoggedDateTimeUtc;

drop table CustomerTable;

FinalData:

Load *,

// key to identify the current row

CustomerID & '_' & EngagementType & '_' & Id as #KeyJoin,

// key to identify the previous line on the current row

CustomerID & '_' & peek(EngagementType) & '_' & (Id-1) as #Key

Resident Ordered;

drop table Ordered;

// only gets the rows for subscribed values

NoConcatenate

Subscriptions:

Load

*

Resident FinalData

where EngagementType = 'Subscribe';

// joins with the cancelled values, by the #KeyJoin key

left join (Subscriptions)

Load

#Key as #KeyJoin,

LoggedDateTimeUtc as EndDateTime

Resident FinalData

where EngagementType = 'Cancellation';

drop table FinalData;

// creates all the fields you need.

FinalTable:

Load

if(not IsNull(LoggedDateTimeUtc) and not IsNull(EndDateTime),Alt(peek(SubscriptionId)+1,1),peek(SubscriptionId)) as SubscriptionId,

if(CustomerID=peek(CustomerID),peek(SubscriptionNo)+1,1) as SubscriptionNo,

CustomerID,

LoggedDateTimeUtc as StartDateTime,

EndDateTime,

if(isNull(EndDateTime),1,0) as Active

Resident Subscriptions

order by

CustomerID,

LoggedDateTimeUtc;

drop table Subscriptions;

This will get me the following:

sample.png

1 Reply
felipedl
Valued Contributor III

Re: Please help :( Identifying multiple subscriptions from customer events table

HI Alex,

Im pasting the code here and attaching the QVW i used to get what you need:

CustomerTable:

LOAD CustomerID,

     EngagementType,

     // Transforms the date you have into a numeric date

     Date#(subfield(LoggedDateTimeUtc,'.',1),'YYYY-MM-DD hh:mm:ss') as LoggedDateTimeUtc

FROM

(ooxml, embedded labels, table is Plan1);

NoConcatenate

Ordered:

Load

*,

// id so i can create key for getting the end date to row directly above the current one

RowNo() as Id,

if (EngagementType='Subscribe',1,0) as [Flag Subscription]

Resident CustomerTable

order by

CustomerID,

LoggedDateTimeUtc;

drop table CustomerTable;

FinalData:

Load *,

// key to identify the current row

CustomerID & '_' & EngagementType & '_' & Id as #KeyJoin,

// key to identify the previous line on the current row

CustomerID & '_' & peek(EngagementType) & '_' & (Id-1) as #Key

Resident Ordered;

drop table Ordered;

// only gets the rows for subscribed values

NoConcatenate

Subscriptions:

Load

*

Resident FinalData

where EngagementType = 'Subscribe';

// joins with the cancelled values, by the #KeyJoin key

left join (Subscriptions)

Load

#Key as #KeyJoin,

LoggedDateTimeUtc as EndDateTime

Resident FinalData

where EngagementType = 'Cancellation';

drop table FinalData;

// creates all the fields you need.

FinalTable:

Load

if(not IsNull(LoggedDateTimeUtc) and not IsNull(EndDateTime),Alt(peek(SubscriptionId)+1,1),peek(SubscriptionId)) as SubscriptionId,

if(CustomerID=peek(CustomerID),peek(SubscriptionNo)+1,1) as SubscriptionNo,

CustomerID,

LoggedDateTimeUtc as StartDateTime,

EndDateTime,

if(isNull(EndDateTime),1,0) as Active

Resident Subscriptions

order by

CustomerID,

LoggedDateTimeUtc;

drop table Subscriptions;

This will get me the following:

sample.png