Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Select records inbetween

Hakimipous
10 - Fireball

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 :

 

F8F9
Family StatusSingle
  
Prorata1
 Jan
 ------------
Gross Salary7061,7839999999997
Car allowance120
 ------------
Net500

 

 

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! 

4 REPLIES 4
Claje
14 - Magnetar

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
12 - Quasar

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.

ConvertToDollars.png

RogerS
Alteryx
Alteryx

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.

Hakimipous
10 - Fireball

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  

Labels