How do I get transactional data from subscription data in Tableau?

Albert Arrutinian

I have a chunk of data with subscriptions like this:

customer_name    start_date   end_date     subscription_amount
A                1-7-2017     31-10-2017   4 USD/month
B                1-8-2017     30-09-2017   2 USD/month
C                1-10-2017    30-11-2017   3 USD/month

I need to convert this to transactional data, so the end result should look like this:

customer_name    payment_date    amount
A                1-7-2017        4 USD
A                1-8-2017        4 USD
A                1-9-2017        4 USD
A                1-10-2017       4 USD
B                1-8-2017        2 USD
B                1-9-2017        2 USD
C                1-10-2017       3 USD
C                1-11-2017       3 USD

I need this conversion to run the analysis in Tableau, but an Excel solution is also acceptable. I don't want to do it manually, but looking for an automated solution using SQL or Python (I'm new to both)

indentation

Using python (you'll need some formatting of the data to exactly match your question, but the idea is here) http://rextester.com/OENHUT92986

Use this comprehensive list to listMonths = [dt.strftime("%Y-%m-01") for dt in rrule(MONTHLY, dtstart=dtstart, until=until)list the first day of each month between 2 dates.

import datetime
import time
from dateutil import parser
from dateutil.rrule import rrule, MONTHLY


data=[
      {"customer_name":"A" ,"start_date":"2017-07-01","end_date":"2017-10-31","subscription_amount":"4 USD/month"},
      {"customer_name":"B" ,"start_date":"2017-08-01","end_date":"2017-09-30","subscription_amount":"2 USD/month"},
      {"customer_name":"C" ,"start_date":"2017-10-01","end_date":"2017-11-30","subscription_amount":"3 USD/month"}
     ]

for datum in data :
    dtstart=parser.parse(datum["start_date"])
    until=parser.parse(datum["end_date"])

    listMonths = [dt.strftime("%Y-%m-01") for dt in rrule(MONTHLY, dtstart=dtstart, until=until)]

    for month in listMonths :    
         print datum["customer_name"],month,datum["subscription_amount"]

will yield:

A 2017-07-01 4 USD/month
A 2017-08-01 4 USD/month
A 2017-09-01 4 USD/month
A 2017-10-01 4 USD/month
B 2017-08-01 2 USD/month
B 2017-09-01 2 USD/month
C 2017-10-01 3 USD/month
C 2017-11-01 3 USD/month

Related

How to get data from Tableau?

Serki I have a bunch of data in Tableau. I want to import it into Excel. I would like to connect Excel directly to Tableau first instead of generating a report and then analyzing that report. Tableau is getting the data from the database, but we can't connect

How to get data from Tableau?

Serki I have a bunch of data in Tableau. I want to import it into Excel. I would like to connect Excel directly to Tableau first instead of generating a report and then analyzing that report. Tableau is getting the data from the database, but we can't connect

how do i get data from the table

Ruben Can someone assist me how to get data from a table in Excel 2013? I have a table called Personal: I want to have a control in a cell like this: (I don't know how to add this control in a cell) So I can select data from the table: I googled a lot before a

how do i get data from the table

Ruben Can someone assist me how to get data from a table in Excel 2013? I have a table called Personal: I want to have a control in a cell like this: (I don't know how to add this control in a cell) So I can select data from the table: I googled a lot before a

How to get data size of SQL MI from subscription using Powershell

Vinny I want to get data size of SQL MI storage account from multiple subscriptions. For example, we have a subscription that has SQL MI configured in the WestUS2 region. Get-AzResourceGroup | ft ResourceGroupName Location ProvisioningState Tags TagsTable Res

Unable to get data from Redis subscription?

Dugi I am trying to get data from a Redis channel by using a subscription on a client application. I am using python with asyncio and aioredis for this. I want to use my subscription to update when a variable of this main application is changed on the server,

Unable to get data from Redis subscription?

Dugi I am trying to get data from a Redis channel by using a subscription on a client application. I am using python with asyncio and aioredis for this. I want to use my subscription to update when a variable of this main application is changed on the server,

How do I get 4 bits from a 16 bit data?

Sajal Singh: I use the getShort() method of the byte buffer to get my short value, I convert it to an int and print the binary value, which is a 16-bit data and I need to access from that 16-bit data 4-bit int value. short bitRateShort = buf.getShort(32 + 2);

How do I get python to store data from multiple people?

Tristan 360 I'm trying to make a discord.py async bot that rewards people with brownie points when asked (hehe). It's supposed to be a local bot for me and my friends, but I don't want to update everyone's brownie point into the code every time I restart it. m

How do I get my data back from my IpcMain

Nelz I am creating a drag and drop react and using electron to send and receive my file data. I received senderId and sender in console.log. How can I get the data to appear? What did I miss? Dropzone.js file const [fileNames, setFileNames] = useState([]); co

How do I get 4 bits from a 16 bit data?

Sajal Singh: I use the getShort() method of the bytebuffer to get my short value, I convert it to an int and print the binary value, it's a 16 bit data that I need to access from that 16 bit data 4-bit int value. short bitRateShort = buf.getShort(32 + 2); int

How do I get my data from the website for parsing?

username public class ODKortrijkWebservice extends AsyncTask < ODKortrijkInterface, Void, String > { private ODKortrijkInterface listener;@ Override protected String doInBackground(ODKortrijkInterface...arg0) { listener = arg0[0];

How do I get a single piece of data from Quickbooks Online?

John Lawrence Aspden I have a client who uses a Quickbooks Online account and he wants to be able to read data from it programmatically. We're using Clojure, so any solution in Java will work, or if necessary go straight to http gets, etc. They seem to have a

angular 2 how to return data from subscription

tsadkan yitbarek ： This is what I want to do. @Component({ selector: "data", template: "<h1>{{ getData() }}</h1>" }) export class DataComponent{ this.http.get(path).subscribe({ res => return res; }) } If getDatacalled DataComponentin , y

angular 2 how to return data from subscription

tsadkan yitbarek ： This is what I want to do. @Component({ selector: "data", template: "<h1>{{ getData() }}</h1>" }) export class DataComponent{ this.http.get(path).subscribe({ res => return res; }) } If getDatacalled DataComponentin , y

angular 2 how to return data from subscription

Zadgan Itbalek This is what I want to do. @Component({ selector: "data", template: "<h1>{{ getData() }}</h1>" }) export class DataComponent{ this.http.get(path).subscribe({ res => return res; }) } If getDatacalled DataComponentin , you m

angular 2 how to return data from subscription

tsadkan yitbarek ： This is what I want to do. @Component({ selector: "data", template: "<h1>{{ getData() }}</h1>" }) export class DataComponent{ this.http.get(path).subscribe({ res => return res; }) } If getDatacalled DataComponentin , y