Home » SQL & PL/SQL » SQL & PL/SQL » Table creation help !!
Table creation help !! [message #214089] Sun, 14 January 2007 22:54 Go to next message
SilyGuy
Messages: 15
Registered: May 2005
Junior Member
Ratio		        1	2	3	4	5
Profitability							
NIM	20%	UL	10	7.49	5.49	3.49	2.49
		LL	7.5	5.5	3.5	2.5	1
ROAA	30%	UL	50	39	29	19	9
		LL	40	30	20	10	0
ROAE	30%	UL	50	39	29	19	9
		LL	40	30	20	10	0
							
Asset Quality							
Gross NPL 50%	UL	50	39	29	19	9
		LL	40	30	20	10	0
Net NPL	 50%	UL	9	19	29	39	50
		LL	0	10	20	30	40




How can i convert this excel structure in meaningful table. I want a table structure based on this given excel sheet. There are 2 groups "Profitability" and "Asset Quality" each group has different ratios (like NIM), each ratio has a weightage (like 20%) then each ratio has UL (upper limit) LL (lower limit) and based on the upper & lower limit there is a point between 1 to 5.

Thanks
Re: Table creation help !! [message #214136 is a reply to message #214089] Mon, 15 January 2007 02:00 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What do you get when you export that as a CSV?

I'm thinking that loading the data via sql*Loader or as an External Table is the best approach.
Re: Table creation help !! [message #214139 is a reply to message #214136] Mon, 15 January 2007 02:06 Go to previous messageGo to next message
SilyGuy
Messages: 15
Registered: May 2005
Junior Member
JRowbottom wrote on Mon, 15 January 2007 03:00
What do you get when you export that as a CSV?

I'm thinking that loading the data via sql*Loader or as an External Table is the best approach.



I don't to load this structure or export it to database. I have this structure in excel and I want to convert this into meaningful table. Table can be more than one also.
Re: Table creation help !! [message #214144 is a reply to message #214139] Mon, 15 January 2007 02:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Two solutions that leap to mind are:
create table meaningful (group_name  varchar2(30) not null
                        ,ratio_name  varchar2(30) not null
                        ,weighting   number(6,2)
                        ,upper_limit number(6,2)
                        ,lower_limit number(6,2)
                        ,ratio       number(3,0));
             
insert into meaningful ('Profitability','NIM',20,10  ,7.5,1);
insert into meaningful ('Profitability','NIM',20,7.49,5.5,2);
insert into meaningful ('Profitability','NIM',20,5.49,3.5,3);
insert into meaningful ('Profitability','NIM',20,3.49,2.5,1);
insert into meaningful ('Profitability','NIM',20,2.49,1,1);
or
create table meaningful (group_name  varchar2(30) not null
                        ,ratio_name  varchar2(30) not null
                        ,weighting   number(6,2)
                        ,limit_1     number(6,2)
                        ,limit_2     number(6,2)
                        ,limit_3     number(6,2)
                        ,limit_4     number(6,2)
                        ,limit_5     number(6,2)
                        ,limit_6     number(6,2));
                        
insert into meaningful ('Profitability','NIM' ,20,10 ,7.49, 5.49, 3.49, 2.49,1);
insert into meaningful ('Profitability','ROAA',30,50 ,39  ,29   ,19   ,9    ,0);
Re: Table creation help !! [message #214146 is a reply to message #214139] Mon, 15 January 2007 02:26 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure I understood all you've said, but: if this Excel spreadsheet was a product of Oracle corp. (instead of Microsoft), it would be called a "matrix report" (made by, for example, Reports Builder). It means that it is result of some kind of calculations (summaries or averages or ...).

Now, looking at this output, you should identify tables and columns which belong to every of those tables. For a beginning, you could try with only one table and put all columns into it. Then, see how to normalize such a model - split that table into two or more tables, put corresponding columns to every one of them, but don't forget to create columns which will "connect" those tables (for example, 'product_id', 'department_id' and similar).

Once you are satisfied with the solution, insert some sample data, write a query (or a report) which *should* produce the same output as this Excel spreadsheet you have.

If you think it is too painful, find the author of this spreadsheet and ask him/her what was the source of it - what did he/she add to what, multiplied or divide with what to get such a result. Perhaps you'll bet a better idea of how to create a data model.
Re: Table creation help !! [message #214149 is a reply to message #214089] Mon, 15 January 2007 02:44 Go to previous messageGo to next message
SilyGuy
Messages: 15
Registered: May 2005
Junior Member
To JRow......thanks for the structure. I think the 1st seems to be more meaningful, but i think i have to repeat this for each Ratio 5 times because there are five UL and LL and for each UL/LL there are 5 different points.

To Little.......this excel sheet is not generated from any database. This is just an excel sheet for reference, I have been told to convert this into a table and there will be one more table (GRADE) that will hold the GRADE_Name and Point for each grade. Based on the point of excelsheet and after some calculation there will be another point which will be compared with the Point in the grade table and a GRADE will be awarded. Hope I am clear.
Re: Table creation help !! [message #214187 is a reply to message #214149] Mon, 15 January 2007 06:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That repetition is the reason that I created the second table.
I noticed that each UL was just slightly smaller than the preceeding LL, so I argued that you didn't actually need to record the LL values, as you could reconstruct them from the ULs. I forgot that you'd need a column to hold the difference between a LL and the following UL, but if you add that into the design, it should work.
Re: Table creation help !! [message #215315 is a reply to message #214187] Sun, 21 January 2007 02:15 Go to previous message
SilyGuy
Messages: 15
Registered: May 2005
Junior Member
JRowbottom wrote on Mon, 15 January 2007 07:04
That repetition is the reason that I created the second table.
I noticed that each UL was just slightly smaller than the preceeding LL, so I argued that you didn't actually need to record the LL values, as you could reconstruct them from the ULs. I forgot that you'd need a column to hold the difference between a LL and the following UL, but if you add that into the design, it should work.


I have tried your 1st table structure and it's working for me. I have been told that there will be almost 40 ratios and there will be 10 different UL and LL for each ratio and 10 different points and the difference between each UL and LL will not be same. And the another twist that has been added to this is that the upper-most UL can be any value and lowest-most LL can be any value as well. How to solve this?

If I follow the 1st suggestion I will have to have 10 UL and 10 LL record for ratio. Do you think this is the only way it can be done? Anyother way of doing this by normalizing or something.

Thanks.
Previous Topic: SQL Query Help
Next Topic: A Problem with using MAX function, please help
Goto Forum:
  


Current Time: Thu Dec 08 03:55:18 CST 2016

Total time taken to generate the page: 0.13355 seconds