05/01/2019 - 06/01/2019

Excel UnPivot option to convert matrix data into simple table

In last session, we discuss the process to convert Matrix Table Data into Simple Excel Table. For details, please find the link :- Convert Matrix Date in Simple table data

Today we will convert Matrix format data as follows


Please insert one blank row in top. Users need to create Unique field data for First Two Heading Rows as follows


Copy the Entire worksheet and paste it another sheet as TEXT
Place the PRODUCT NAME in A1 cell
Remove 2 and 3 number rows


Press CTRL+T


Select POWER QUERY FROM TABLE


Select the first column and choose UNPIVOT OTHER COLUMNS


Now need to Split B column data


Therefore our requirement is solved and we will process the data in excel.

Click on Closed and Apply

Excel Power Query will copy and paste data in new excel sheet.

Please find the video


If users copying data from PivotTable ot get data in matrix format, users unable to use PivotTable in MATRIX format data. Today, I want to share process How to Convert Matrix data in simple excel file.

First view the sample data in matrix format. If we need to create simple table, we need to follow copy paste manually and it is time consuming task. In excel, we can easily conver matrix data in simple table.

STEP -1
Please follow the sample data which I created



Step -2
Keep mouse pointer in any cell and press CTRL + T


Output as per below image




Step - 3
Please select POWER QUERY
After Excel 2010 version, Power Query are available in 2010, 2013, 2016 with Office 365. To download POWER QUERY, please visit the link :- https://www.microsoft.com/en-in/download/details.aspx?id=39379



Step - 4
Select POWER QUERY
Then select From Table as below image



Step-5
In POWER QUERY, data shows as follow




Step - 6
Selete the First Column "PRODUCT NAME"
Go to TRANSFORM menu
Choose option "UNPIVOT OTHER COLUMN" as follows



OUTPUT will be



You are finish to create simple data table from Matrix/PivotTable.

Tomorrow I will share PART - 2 on CONVERT COMPLEX MATRIX DATA INTO SIMPLE DATA TABLE IN EXCEL


Hello Friends, today I am going to share SQL syntax to create Table by copying data from existing table. Some times it require to create new table for data according to data nature.

Say, you have CUSTOMER table which contains data for MALE, FEMALE and CHILD category. Now you require to create three (3) new table as named CUSTOMER_MALE, CUSTOMER_FEMALE and CUSTOMER_CHILD

My existing table name is DEMO_CUTOMERS and I will create new Customer table

TASK 1
CREATE NEW TABLE WITH ALL DATA FROM EXISTING TABLE

Connect to Oracle Express Edition 11g Database and using my schema name PIJUSH

See my existing Table by using SQL comment SELECT * FROM DEMO_CUSTOMERS



Query which I used for copying data from one table to new table
CREATE TABLE NEWCUSTOMERTABLE AS
SELECT  CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME
FROM DEMO_CUSTOMERS

Run the query

Query create new table names as NEWCUSTOMERTABLE

Now view the data in new table, run query SELECT * FROM NEWCUSTOMERTABLE
Result is



TASK 2
CREATE NEW TABLE WITH  SPECIFIC DATA FROM EXISTING TABLE

Create second table where customer name is WILLIAM

Query which I used
CREATE TABLE NEWCUSTOMERTABLE_2 AS
SELECT  CUSTOMER_ID, CUST_FIRST_NAME, CUST_LAST_NAME
FROM DEMO_CUSTOMERS WHERE CUST_FIRST_NAME = 'William'

Run query

See the result


You will notice that, in case of first query, all customer data copy from old table and paste in new table. But for second query, only specific data as mentioned in condition "WHERE CUST_FIRST_NAME = 'William' inserted into new table.



When users click on SESSION, oracle application ask for session ID and password. The default user HR session are locked.


Click on ALL PROGRAM
Find the Oracle Database 11g Express Edition
Click ob Run SQL Command Line


Type CONNECT and execute the query



  • Enter username like system
  • Enter password - password of the database which you input at the time of database installing
  • After password press enter
  • Connected

  • UNLOCK USER
  • Run SQL query "ALTER USER HR ACCOUNT UNLOCK"
  • Press Enter

  • CHANGE USER PASSWORD


Enter in Oracle Database.

Thanks


In my last articles, I shared notes regarding HOW TO CREATE GROUP COMPANY. In this lesson, I am going to show how to create NEW COMPANY in SAP.

As SAP mythology, users need to maintain financial statement and legal compliance at Company Code level. Therefore as per legal entity, users need to DEFINE COMPANY CODE. Also Company Code used to define company as per continent or region. For example AUDI INDIA LTD, AUDI UK LTD OR AUDI USA LTD.

We will copy from SAP Default company and create our new company. After creation, we will modify necessary change as per our company profile. We will copy from existing company because it will help us to copy SAP default table from existing company to new company.

Before creating the New Company Code, please create Group Company for Consolidated Financial Report, please visit How to Create Group Company in SAP

Therefore, the Company Code creation process divided in two step
STEP -1. Copy from Existing Company
STEP -2. Edit company profile data after copy from exsting company

STEP -1
COPY COMPANY CODE FROM EXISTING COMPANY


  • After logged in, run comment SPRO in t-code bar
  • Follow the navigation path as below image or run T-Code OX02 


  • Select Copy, Delete, Check company code and Double click this option


  • Click on Copy icon 
  • After compiling the table, this window will open




  • Insert From Company Code, for India I am selecting 4300 Company Code
  • New 4 Digit Alpha Numeric Number insert in TO COMPANY CODE field, I am inserting R111 i.e. my new company code will be R111
  • Click continue

  • We will create our own G/L master. So please select NO

  • Please select NO if you would not allocate different currency

  • Click on Continue by cliek tick icon

  • Application shows warning message. Read the message and click on Continue.

  • We will create our own Number Ranges for posting, please ignore this step and select NO

  • Now create our won TRANSPORT REQUEST number. Please save the number in one place. When we are going to transfer configuration from TESTING SAP CLIENT to LIVE SAP CLIENT, we need to use the TRANSPORT REQUEST NUMBER.

  • Company Code 4300 tables copied to R111 successfully.


STEP -2

EDIT COMPANY CODE DETAILS


  • After complete Step 1, click on BACK button

  • Double click on EDIT COMPANY CODE DATA

  • Click on Position and search the company R111

  • Double click on company name and edit the data as require
  • Click on Save

Hello Friends, Today we will discuss the meaning of "Define Company" and process to create Company.

Meaning of Company

Company is the group of Company in which we will create our consolidated financial statement. The word "Group of Companies" means in one Company we will assign multiple Company i.e. Company Code. COMPANY is the equivalent to legal entity. Therefore, COMPANY is the parent of the all COMPANY CODE.

For example,
ABC Industries Ltd - is the Parent or Group Company. The group company having three different legal entity like below:-

ABC India Ltd - is the child organization or told as COMPANY CODE
ABC UK Ltd - is the child organization or told as COMPANY CODE
ABC Canada Ltd - is the child organization or told as COMPANY CODE

Process to create COMPANY in SAP
  • After logged in in SAP. Type SPRO in comment bar and press entry
  • Expand the ENTERPRISE STRUCTURE and select DEFINE COMPANY option. Please follow below SNAP. Users can also search form by type \nOX15 in comment boss and press enter.

  • Below page will open

If you need to know any field (like language) specification or details shows in image, please add your comment.
  • Insert available data 
  • Fill necessary fields and SAVE
  • After save, click New for generate TRANSPORT REQUEST NUMBER

  • Insert short description and click on SAVE
  • Copy the TRANSPORT REQUEST NUMBER in excel file.
  • Click on CONTINUE icon






Sometimes my management ask to create PivotTable and share PivotTable data in a excel workbook. When I copy data from Pivot Table and paste the data into different excel sheet, I faced problem due to blank cell. Therefore me and my management team can't use filter option. 

So today I will share tips to fill blank cell with above or below cell data. Also users can fill the blank cell data with fixed value with simple replace option.

Please follow the data which is copied from PivotTable

Now I need to fill the blank cell with the above cell value

STEP -1
Choose the selection area

STEP - 2
Access FIND AND SELECT
Seelct GoTo Special


STEP - 3
Select Blank

STEP - 5
Now need to select Above/Below cell 

STEP - 6
Type =Cell Code like below

Press CTRL + ENTER

STEP - 7
Follow the result

If you want to fill the cell with any specified value, users need to follow the REPLACE function


STEP - 8,
Replace the blank cell with 0 value


Clink on Replace ALL



Done

Contact Form

Name

Email *

Message *

Powered by Blogger.
Javascript DisablePlease Enable Javascript To See All Widget