number slicing

[redballoon]

Hi,

I want to import two number strings and make one new string

The strings are like this

two 6 figure numbers

125678 875643

I want to take the second third and fourth digit of each number

125678 875643

and create two new numbers

256 756

The numbers are the result of a lookup and normally you would return the full 6 digit number but I just want the 3 digits from each...

How do I do that ?

Thanks.

[Rick Whitelaw]

Check the Middle function.

[Lee Smith]

Middle ( num1 ; 2 ; 3 ) & " "& Middle ( num2 ; 2 ; 3 )

Remove " "& if no space is needed

p.s. not sure if you have these raw numbers already combined, if so it would be the same idea

Middle ( oneNum ; 2 ; 3 ) & " "& Middle ( oneNum ; 8 ; 3 )

HTH

Lee

[redballoon]

Middle ( num1 ; 2 ; 3 ) & " "& Middle ( num2 ; 2 ; 3 )

Remove " "& if no space is needed

p.s. not sure if you have these raw numbers already combined, if so it would be the same idea

Middle ( oneNum ; 2 ; 3 ) & " "& Middle ( oneNum ; 8 ; 3 )

HTH

Lee

wow thanks I will have a shot at this.

[redballoon]

Fabuloso !

Thanks...

Now I have another possibly harder question...

I have a dataset that I have imported into Filemaker, it is basically grid reference data and wind speed...

the dataset goes like this

f1 f2 f3 f4 …... f102

easterly northerly 0 1 99

100 674 1.5 .5 5.5

200 674 2.0 1.5 3.5

so our grid reference is made up of the northerly + easterly combined

so the first value we see is 674100 this has value under f3 of 1.5

Now the tricky bit is the value of the grid reference is incremented by 1 to 674101 and we see that as the next value under f4 as .5 674102's value would be under f5 etc…to 674199.

I want to index the whole lot, i.e. create a table with the reference i.e. 674100 etc…. and the result for all the fields in the table...

Does anyone know how I can do that ?

Thanks.

[Lee Smith]

Hey Red

I think Im understanding some of what you are asking, but why not post a copy of your source file to help us see the actual data?

To post a file, just follow these steps.

Step 1

Find the file on your hard drive, and zip it.

Step 2

Do not use the Quick Reply, BUT instead, choose the More Reply Option

Step 3

Click on Attached Files and this will take you to your hard drive to locate your file that you want to attach and that you previously zipped. Select the file, and then click Attach This File, and then click Add Reply.

If you should have a problem write me a private me.

[comment]

I want to index the whole lot

Not sure what you intend to do with this data eventually. However, to provide the best access to it, I'd suggest you break it up into individual records - i.e. a table with only 3 fields:

• Northerly

• Easterly

• Speed

IIUC, that means 100 times more records than what you have now.

I can see no advantage in combining Northerly and Easterly into a single number; in general it's best to follow the 'one fact per field' thumb rule.

[redballoon]

The file is 120 MB in size even when zipped,

also If I convert those 3 fields it would be 300 more records, making it 3,000,000 that's fine but I need to get at the data to format it.

here is a snap shot of the data file… (in Filemaker)

Not sure what you intend to do with this data eventually. However, to provide the best access to it, I'd suggest you break it up into individual records - i.e. a table with only 3 fields:

• Northerly

• Easterly

• Speed

IIUC, that means 100 times more records than what you have now.

I can see no advantage in combining Northerly and Easterly into a single number; in general it's best to follow the 'one fact per field' thumb rule.

That will produce 3,000,000 records currently there are 10,000 If I do that (quite happy to) is there a way I can pull this out of the file… at the moment it comes as an els file and I have simply imported….

As you can see the

Hey Red

I think Im understanding some of what you are asking, but why not post a copy of your source file to help us see the actual data?

To post a file, just follow these steps.

Step 1

Find the file on your hard drive, and zip it.

Step 2

Do not use the Quick Reply, BUT instead, choose the More Reply Option

Step 3

Click on Attached Files and this will take you to your hard drive to locate your file that you want to attach and that you previously zipped. Select the file, and then click Attach This File, and then click Add Reply.

If you should have a problem write me a private me.

[comment]

It all depends on what you intend to do with the data once it is organized.

[redballoon]

OK I have the logic figured

The data I want to access is in a large table

I want to access the data using 3 references

where the value of f2 = X + f1 = y

I want to get at a value in the resulting field at point a + 2

so if a was 4 then I would get my value from column f6

where f2 = X + f1 = y

That should be achievable I think.

[redballoon]

Right I have narrowed this down to a simple...

GetField fromtable::fieldname

where field name is the contents of a field from my current record view….

I have a field "thisField" with contents "f8" the contents is generated from various other data.

I want to look up the contents of the field and plonk it into a filed in my current view

I am trying to do this with a calculated field, really don't want to have to script it, I have 3 million records….

[comment]

If you want to do this using your current structure, you'll need to define a relationship matching on North, then get the data from the appropriate field using a calculation like =

GetField ( "YourDataTable::f" & East + 3 )

[redballoon]

Thanks that works a treat

[redballoon]

If you want to do this using your current structure, you'll need to define a relationship matching on North, then get the data from the appropriate field using a calculation like =

GetField ( "YourDataTable::f" & East + 3 )

Thanks that works for one lookup but not the rest, is there any reason why this might not work on more than one lookup ?

[comment]

I am not sure what you mean by "one lookup". If you are using a calculation field, it will recalculate whenever you change the "local' North/East values.

[redballoon]

The top calculation works, the next two do not...

I have taken them out and put them back in again and again...

[redballoon]

OK, also I can see about 50% of the lookups fail on the first lookup, I have no idea why, about 40% work fine / perfectly, however the rest return a result of 0 when I actually look at the table I can see the result that should have been retrieved.

I have two relationships with the lookup table, so I can identify the record, since f1 and f2 in my image above indicate the actual record required I have to look them up and then I pull in the right field from the dataset….

[comment]

Sorry, I don't follow. Please post a file showing the problem.

[redballoon]

ill try its about 3oo MB in size

[comment]

A simple file showing the problem (and preferably nothing else) will be fine.

[comment]

In fact, here's a file that shows how it can work:

LookupMatrix.fp7.zip

[redballoon]

OK,

So basically I am trying to get the record as discussed just for 10 M/s

maybe there is something wrong with my lookup...

OK,

So basically I am trying to get the record as discussed just for 10 M/s

maybe there is something wrong with my lookup...

10ms database Converted Clone.zip

[comment]

I haven't installed v.12 yet, so it would be best if you looked at my file and compared it to yours.