Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
CustomerID | EngagementType | LoggedDateTimeUtc |
---|---|---|
1 | Subscribe | 2018-08-01 11:46:08.210 |
1 | Cancellation | 2018-08-05 11:46:08.210 |
1 | Subscribe | 2018-08-10 11:46:08.210 |
1 | Cancellation | 2018-08-15 11:46:08.210 |
2 | Subscribe | 2018-09-01 11:46:08.210 |
2 | Cancellation | 2018-09-10 11:46:08.210 |
2 | Subscribe | 2018-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)
SubscriptionID | CustomerID | SubscriptionNo | StartDateTime | EndDateTime | Active |
---|---|---|---|---|---|
1 | 1 | 1 | 2018-08-01 11:46:08.210 | 2018-08-05 11:46:08.210 | 0 |
2 | 1 | 2 | 2018-08-10 11:46:08.210 | 2018-08-15 11:46:08.210 | 0 |
3 | 2 | 1 | 2018-09-01 11:46:08.210 | 2018-09-10 11:46:08.210 | 0 |
3 | 2 | 2 | 2018-09-12 11:46:08.210 | getdate() | 1 |
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:
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: