Preppin’ Data — 2019W13

Input

Most clean data I have seen in some exercises.

Daily level transactional data
Dimension table with Name and Credit Info

Steps for Solution

  1. Join the 2 tables using the account Key
  2. The tricky part is the no. of days with negative balance and no. of days below credit limit. This cannot be calculated once you aggregate your data on different timelines. So, we need to calculate this on the current level as flags and aggregate them on different timeline levels.
  3. Also, the most difficult part I has to learn was how-to split the branches instead of adding steps in between branches. You need to click on the + sign on the box rather than on the line.
  4. Calculate start dates, based on week, month and year truncation. Average the balance and transaction as instructed and sum up the flags for negative balance and below credit cases.
  5. Make sure to remove the date field from the grouped columns , else your granularity will not change.

Solution Flow

Output

Daily Out
Quarterly Output
Monthly Output
Photo by Aziz Acharki on Unsplash

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

5 Reasons Marijuana Stocks Could Lose You Money in 2019

Younger Americans Are Growing More Reliant on Social Security — but Should They Be?

The Risks of Margin Trading

Benefits of Trading Futures vs ETFs

Credit card APR?

Getting A Side Hustle Is the Quickest Way To Paying Off Debt

10 Passive Income Ideas to Build Wealth in 2021

Saving For That Life-Changing Trip Travel With Cooky

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Jeevan Krishna

Jeevan Krishna

More from Medium

Day 20: Reflecting & Bar Charts

2022 Evictions Revisited — Tableau Edition!

Getting Started with Tableau

Five Ways Changes in Data Can Wreak Havoc on Your Dashboards