Select records inbetween

Hello,

I would like to know if there is an easy way to select records between a certain data

For exemple I have a table that looks a bit like this :

F8	F9
Family Status	Single

Prorata	1
	Jan
	------------
Gross Salary	7061,7839999999997
Car allowance	120
	------------
Net	500

I would like to select records only between Jan and Net, or better between the ------------

Reason is that I need to transform those numbers and round them to 2 decimals, without touching to the others variables.

I know the easy would be to put a record ID and then select with the > or = from the filter tool.

But I can't rely on record position (as I'm used to), as I have to make my workflow the most dynamic possible and the position of my records is likely to change with other data set.

If anyone has a formula, or a way around to solve this,

Thanks!

Tips and Tricks

Best Practices

Accepted answers

Philip

Hi @Hakimipous

Here's one way to solve it.

1. Create a new grouping field that captures if it is a month or a Net.

2. Use the Multi-Field formula tool to fill in the grouping field.

3. Find the numbers within the month groupings and put them in a Fixed Decimal field rounded to the nearest 2 decimal points.

4. Convert the numbers back to strings in the F9 field.

5. Remove the temporary fields.

RoundToDollars.yxmd

All comments

Claje

The Multi-Row Formula tool might be able to help you here.

Something along these lines:

Create a new field called "NeededInfo" - i'd make this an integer or binary value personally, but it is up to you

Then you could write a formula something like this:

IF [F9] = '------------'
   Then
     IF [Row-1:NeededInfo] = 1 Then 0
        Else 1
     ENDIF
   ELSE [Row-1:NeededInfo]
ENDIF

After that, you could apply a Filter Tool and filter for NeededInfo = 1.

This should give you only the information between these sets of dashes.

I haven't really tested this formula, so you may also need to filter for [F9] != '------------' after that point.

There are probably some improvements you could make to this design, but this should give you a starting point

Philip

Hi @Hakimipous

Here's one way to solve it.

1. Create a new grouping field that captures if it is a month or a Net.

2. Use the Multi-Field formula tool to fill in the grouping field.

3. Find the numbers within the month groupings and put them in a Fixed Decimal field rounded to the nearest 2 decimal points.

4. Convert the numbers back to strings in the F9 field.

5. Remove the temporary fields.

RoundToDollars.yxmd

RogerS

First I noticed that that a comma is used instead of period so I used a formula to change this. ReplaceChar([F9], ',', '.') I then used a second formula to round anything that has 1 or more number followed by a period and then followed by more then 2 numbers since these will need to be rounded. if REGEX_Match([F9], '\d+[.]+\d{2}+\d+') then tostring(Round(ToNumber([F9]),.01)) else [F9] endif

I had to use the tonumber function since this is stored as a string.

I used another solution that is more complicated if you want to take a look at the attachment.

select record between.yxmd

Hakimipous

Works like a charm @Philip thanks,

I added .* in the Regex_Match expression to make sure to capture negative numbers as well

Thanks to @RogerS @Claje , I'll try your method as well and see what fits better but those are exactly what I was looking for, a way not to rely on record position

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703