A magical Excel trick for flattening data copied from a PivotTable report in tabular layout into usable format

Magical Trick for Data Flattening in Excel

Here is a scenario that I come across sometime ago when I received an Excel file with some apparently messy data for further processing to generate another report. The data in the file appear similar to below screenshot. This data is self-explanatory. It’s the sales in units and dollars of each product under each product category made by each sales person. If you observe the data, for all the sales made by each sales person, name of the sales person is listed only once. Same goes with product category appearing only once for each product.

Now the challenge is to flatten the data in a way that for each product row, respective product category and sales person name should get repeated. Only then, it becomes consumable in downstream process for further preparation of another report. By the way, this is just some fictitious small amount of data. The file I received has more than a few thousands of records, which means it’s not feasible to perform this task manually.

By the way, this kind of data mess happens when you copy the data from a PivotTable report in Tabular Layout, with Repeat All Item Labels option not selected under Layout group of Design tab, +/- toggle button disabled under Show group of Analyze tab of PivotTable Tools ribbon; and then paste the values of such copied data to different location for the further sharing, like it was shared with me.

ExcelDataFlatteningSource

Here is a magical trick to complete this task accurately in just a couple of steps:

Step.1. Apply filter to the header row. Click on Salesperson field filter drop down. Deselect all items and then select only blanks. Hit OK.

The data looks as below after this step.

image

Step:2. After applying the filters, observe that the cell address of the first blank cell in Salesperson column is A4 (boxed in red). This means that the cell above A4 (i.e., cell A3) is the first non-blank cell. This gives us a logic that the value in A4 should have been the same as the value in cell A3. So, just enter the formula =A3 in cell A4. The same logic applies for all the other blank cells too. So, copy the formula to the rest of the blank cells. After this, clear the applied filters from salesperson column header. That works the MAGIC..!! This completely fills up all the blank cells in the Salesperson column accurately as in the below screenshot:

image

Repeat these two steps for Category column as well. The magical trick works there too..!!

For more such tricks follow the blog. Hit the likes if you like the trick. Happy Learning..!!

Advertisements

Generating Date Dimension data in Microsoft Excel

Generating Date Dimension data in Excel

Having shared the SQL technique and the script for generating data for Date Dimension for use in self-service BI and Analytics in my , here I share another technique for the same purpose but for those of you who are not familiar with SQL.

Here is the that lets you create Date Dimension data for two years from a given start date. All that you have to do is enter the desired start of the year in date format in cell B3. Rest is taken care that everything refreshes automatically.

Hit the like stars if you find it useful. Share the post further to spread the learning. Follow the analyst arsenal for effortless continued learning. Happy Learning..!!

Oracle SQL script to generate Date Dimension data for use in Microsoft Excel Data Models

Date dimension is that one dimension or business perspective without which an Excel Data Model cannot ever exist. Here is an Oracle SQL script to generate the data for a Date Dimension. All that you have to do is change the start date and end date values in the in the last line of the below SQL query and execute it. Try it and experience the magic..!! Please leave your suggestions, comments and any enhancements that you might want to see in this.

Here is the downloadable script on Oracle LiveSQL:

/***********************
Description: Date Dimension Data Generation
Date Created: 14/Nov/2016
Author: Balakrishna Sappa
************************/

CREATE OR REPLACE TYPE DATE_OBJ AS OBJECT(DATE_KEY VARCHAR2(8), DATE_DT DATE, DATE_TYPE VARCHAR2(10), DAY_OF_MONTH SMALLINT, CAL_DAY_OF_YEAR SMALLINT, DAY_OF_WEEK_FULL_NAME VARCHAR2(9)
, DAY_OF_WEEK_SHORT_NAME CHAR(3), CAL_MONTH_FULL_NAME VARCHAR2(9), CAL_MONTH_SHORT_NAME CHAR(3), CAL_MONTH_ENUM SMALLINT, CAL_QTR_NAME CHAR(2), CAL_QTR_ENUM SMALLINT, CAL_HYR_NAME CHAR(2)
, CAL_HYR_ENUM SMALLINT, CAL_YEAR INT, START_OF_MONTH DATE, END_OF_MONTH DATE);
CREATE OR REPLACE TYPE CALENDAR_DATES IS TABLE OF DATE_OBJ;
CREATE OR REPLACE FUNCTION UDF_CALENDAR_GENERATOR(START_DATE IN DATE, END_DATE IN DATE)
RETURN CALENDAR_DATES
IS
CALENDAR_DATE CALENDAR_DATES;
BEGIN
SELECT CAST(MULTISET(WITH RCTE_DATES (DATE_DT)
AS
(SELECT START_DATE AS DATE_DT
FROM DUAL
UNION ALL
SELECT DATE_DT + 1 AS DATE_DT
FROM RCTE_DATES
WHERE DATE_DT + 1 <= END_DATE
)
SELECT CAST(EXTRACT(YEAR FROM DATE_DT) AS CHAR(4)) || SUBSTR(’00’ || EXTRACT(MONTH FROM DATE_DT), -2,2) || SUBSTR(’00’ || EXTRACT(DAY FROM DATE_DT), -2, 2) AS DATE_KEY
, DATE_DT
, ‘DATE’ AS DATE_TYPE
, EXTRACT(DAY FROM DATE_DT) AS DAY_OF_MONTH
, CAST(TO_CHAR(DATE_DT, ‘DDD’) AS SMALLINT) AS CAL_DAY_OF_YEAR
, TO_CHAR(DATE_DT, ‘DAY’) AS DAY_OF_WEEK_FULL_NAME
, TO_CHAR(DATE_DT, ‘DY’) AS DAY_OF_WEEK_SHORT_NAME
, TO_CHAR(DATE_DT, ‘MONTH’) AS CAL_MONTH_FULL_NAME
, TO_CHAR(DATE_DT, ‘MON’) AS CAL_MONTH_SHORT_NAME
, EXTRACT(MONTH FROM DATE_DT) AS CAL_MONTH_ENUM
, ‘Q’ || TO_CHAR(DATE_DT, ‘Q’) AS CAL_QTR_NAME
, CAST(TO_CHAR(DATE_DT, ‘Q’) AS SMALLINT) AS CAL_QTR_ENUM
, ‘H’ || CASE WHEN EXTRACT(MONTH FROM DATE_DT) IN (1,2,3,4,5,6)
THEN 1
ELSE 2
END AS CAL_HYR_NAME
, CASE WHEN EXTRACT(MONTH FROM DATE_DT) IN (1,2,3,4,5,6)
THEN 1
ELSE 2
END AS CAL_HYR_ENUM
, EXTRACT(YEAR FROM DATE_DT) AS CAL_YEAR
, TRUNC(DATE_DT, ‘MONTH’) AS START_OF_MONTH
, TRUNC(ADD_MONTHS(DATE_DT, 1), ‘MONTH’)-1 AS END_OF_MONTH
FROM RCTE_DATES
UNION
SELECT ‘999999’ AS DATE_KEY
, TO_DATE(’31-DEC-9999′, ‘DD-MON-YYYY’) AS DATE_DT
, ‘NA’ AS DATE_TYPE
, 0 AS DAY_OF_MONTH
, 0 AS CAL_DAY_OF_YEAR
, ‘NA’ AS DAY_OF_WEEK_FULL_NAME
, ‘NA’ AS DAY_OF_WEEK_SHORT_NAME
, ‘NA’ AS CAL_MONTH_FULL_NAME
, ‘NA’ AS CAL_MONTH_SHORT_NAME
, 0 AS CAL_MONTH_ENUM
, ‘NA’ AS CAL_QTR_NAME
, 0 AS CAL_QTR_ENUM
, ‘NA’ AS CAL_HYR_NAME
, 0 AS CAL_HYR_ENUM
, 9999 AS CAL_YEAR
, TO_DATE(’31-DEC-9999′, ‘DD-MON-YYYY’) AS START_OF_MONTH
, TO_DATE(’31-DEC-9999′, ‘DD-MON-YYYY’) AS END_OF_MONTH
FROM DUAL
)
AS CALENDAR_DATES
)
INTO CALENDAR_DATE
FROM DUAL;
RETURN CALENDAR_DATE;
END;

Below is as screenshot of the results of this SQL executed in Oracle’s fee online SQL learning and execution tool: Oracle Live SQL.

datedimensiondatagenerator

 

A White Paper – Solving Relational Division problem in SQL – The Analyst Way

Hi readers..!!

Here I go posting a white paper on one of the classical and intellectually challenging problems that an analyst might come across. The paper is titled – “Understanding and Solving Relational Division problems in SQL – The Analyst way”.

Hope you enjoy reading this paper.

Please leave your comments and suggestions, if any, here.

Will be back with more stuff. Stay tuned to the blog. Until then, happy learning..!!