?
Solved

capture values in calculated column

Posted on 2013-02-05
7
Medium Priority
?
247 Views
Last Modified: 2013-02-06
I have a column that called "Metric ID" and it contains data as you see below.  The problem I'm having comes when I sort.  It sorts incorrectly because it's a single line of text.

Wrong
1.1.1.1
1.2.1.1
10.1.1.1
16.1.8.5
17.2.9.1
2.1.1.1

Correct (Needs to be sorted like this)
1.1.1.1
1.2.1.1
2.1.1.1
10.1.1.1
16.1.8.5
17.2.9.1

Any ideas?

I have an idea with calculated columns but not sure how to do it.
I will create 4 calculated columns.
calculatedColumn1 = first value before the period
calculatedColumn2 = 2nd value before the period
calculatedColumn3 = 3rd value before the period
calculatedColumn4 = 4th value after the period

How do I capture each value using a calculated column?
0
Comment
Question by:Isaac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 38857460
Hi,

the first two columns can be calculated like this:
First part:
=LEFT([Metric ID],(SEARCH(".",[Metric ID],1)-1))

Second part:
=LEFT(RIGHT([Metric ID],(LEN([Metric ID])-SEARCH(".",[Metric ID],1))),(SEARCH(".",RIGHT([Metric ID],(LEN([Metric ID])-SEARCH(".",[Metric ID],1))),1)-1))

and so on.

HTH
Rainer
0
 
LVL 5

Author Comment

by:Isaac
ID: 38857466
Here's how I got the 1st number:


=if(find(".",[metric id])=2,left(1),left(2))
0
 
LVL 5

Author Comment

by:Isaac
ID: 38857491
Actually, what I did was wrong.

Yours is right!

I will try and get the 3rd and 4th.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 44

Accepted Solution

by:
Rainer Jeschor earned 2000 total points
ID: 38857540
Hi,
found a little bit simpler way:
First part
=LEFT([Metric ID],(SEARCH(".",[Metric ID],1)-1))

Second part
=LEFT(RIGHT([Metric ID],(LEN([Metric ID])-(LEN(FirstPart)+1))),(SEARCH(".",RIGHT([Metric ID],(LEN([Metric ID])-(LEN(FirstPart)+1))),1)-1))

Third part
=LEFT(RIGHT([Metric ID],(LEN([Metric ID])-(LEN(FirstPart)+1+LEN(SecondPart)+1))),(SEARCH(".",RIGHT([Metric ID],(LEN([Metric ID])-(LEN(FirstPart)+1+LEN(SecondPart)+1))),1)-1))

Last part
=RIGHT([Metric ID],(LEN([Metric ID])-(LEN(FirstPart)+1+LEN(SecondPart)+1+LEN(ThirdPart)+1)))

Open in new window


The above four columns should now have the correct values.

HTH
Rainer
0
 
LVL 5

Author Comment

by:Isaac
ID: 38857595
Actually, there is no need for me to get the 3rd and 4th column because I can only sort up to  columns.

Anyway, that did not work as i thought it would.  Even though I sorted the calculated column, it still sorted wrong.

See below.  "Metric Value 1" is my calculated value that extracts the first value.

Here's what happens when I sort by "Metric Value 1"....

Metric ID              Metric Value 1
1.1.1.1                               1
1.2.1.1                               1
10.1.1.1                             10
16.1.8.5                             16
17.2.9.1                             17
  2.1.1.1                             2

Below is an image of what I created
metric value 1
Here's how i sort
sort
Any ideas of why it's not sorting?

doesn't make sense....
0
 
LVL 5

Author Comment

by:Isaac
ID: 38858000
How can I do the following?
Remove all the "." except the first one.  I can probably make that column a decimal and sort it that way.

1.1.1.1 => 1.111
1.2.1.1 => 1.211
10.1.1.1 => 10.111
16.1.8.5 => 16.185
17.2.9.1 => 17.291
2.1.1.1 => 2.111
1.1.3.1 => 1.131
1.1.1.5 => 1.115
0
 
LVL 44

Expert Comment

by:Rainer Jeschor
ID: 38858234
Hi,
OK - but as I have seen you have already entered another question on the sorting issue.
I think your initial question in splitting up the values is answered.

I will post my proposed solution into the other question.

Thanks and HTH
Rainer
0

Featured Post

Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There is one common problem that all we SharePoint developers share: custom solution deployment. This topic can't be covered fully in this short article, so all I want to do in this one is to review it from a development-to-operations perspectiv…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question