This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
The highly anticipated Alteryx Community tradition is back! We hope you'll join us!
Learn MoreHello,
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!
Solved! Go to Solution.
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
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.
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.