community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE
SOLVED

Select records inbetween

Alteryx Partner

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! 

Magnetar
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

 

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

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.

Alteryx Partner

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