How To Add Serial Number In Alteryx
Manases Blakemore
I have an output file that is returning a few columns, one with numbers in them. The problem I am encountering is that the column with numbers is coming out as text. Is there an option to have the output file have the column with numbers return numbers instead of text format?
I currently have a list of dates 1/1/2020, 1/2/2020, 1/3/2020....etc listed in excel. However, I want to turn those dates into a number just like how excel does it when I change the format of 'Date' to 'General'. Is there a way to dynamically change these dates to a string of numbers in Alteryx? There will be a lot of numbers, so I would like to build a formula to change all at the same time.
Hi. I came up with the exact same solution through some trail and error but i can't for the life of me understand why it's a "+2" at the end not a "+1". Can you explain this please?! It's driving me nuts!
You need to minus 2 off the number provided by Excel, due to the issue with 29th Feb 1900 and the inclusiveness of the start/end dates. (1900-01-01 is day 1 not day 0, and 1900 was not a leap year but excel calculates it wrongly).
Essentially what I'm trying to do is generate rows for each person based on the number of days they are away on holidays I have attached a small piece of the workflow with bit of the data I'm using as a sample. I can't wrap my head around how something liked this could be done
Thank you guys so much for the rapid response I see exactly what you both did there I was just wondering instead of having just numbers in the new rows is it possible to have the day of the week instead?
Hey @DataIsFun, does something like this look right? We're adding the generated row number (-1 to compensate for not being 0-based) to the date and then formatting the date as the day of the week it corresponds to:
I have a file that I used a text to column to separate numbers by comma, then I used a data cleanse to get rid of any leading and trailing whitespace, plus tabs, line breaks, and whitespace. However, once I use the Select tool to change the numbers from string to a numerical data type, on some of the numbers I get an error that says it is not a number (Field1:11050 is not a number.) and it deletes the number from the cell. I exported the data before applying the Select and looked at the fields and there aren't any additional spaces, characters, etc so not sure why it's not converting. Is there any way I can keep it from deleting the number even if it doesn't convert? Or any ideas on why it won't convert it from a string to a number.
You can't rely on simply removing everything but numbers and having what is remaining be able to be converted to a number using a Select tool. A better method to convert the cleansed field from string to numeric is to use a Multi-Field Formula tool with the field(s) to convert selected, uncheck the Copy Fields and Add box, change the output type to an appropriate numeric type, and use the formula ToNumber([_CurrentField_]).
Post some example data - @T_Willins instructions should solve your problem - using a MULTI-FIELD FORMULA vs a regular formula is key.... Can you also confirm that any . or , are correct in terms of your localization settings? Also that assumes you aren't bringing in non-integers as integers...
FYI - I'm seeing one of your numbers has an error (11050) - I think one of the characters isn't a 1 or isn't a 0 - I'm looking into it... It's my only conversion error on a multi-field after datacleansing btw.
Your worksheet has a non-breaking space. This can happen with downloaded reports. If you look at cell A232 in your Excel workbook and click on the first 1 in 11050, then backspace, you can see the it takes 2 clicks to move back one whitespace. You can cleanse this with a RegEx Replace. See the attached workflow that runs without error.
I'm late to the party, but I'm posting this in case other people come to this thread looking for ISO week numbers. I've attached a workflow which has the test values above and a series of calculations that determine the correct ISO week number (basically @JohnJPS's formula broken down into steps--he has the same steps in his own sample workflow.)
A little late to this conversation. I believe the ISO weeks were off in 2015 due to an anomaly that causes a 53 week on most fiscal calendars every 6 years.or so. I am struggling with a similar issue being that I do not have a full date for my weeks from previously stored data. The data is in the format of wk/yyyy is it possible to use the %W or %U Function on this format to make it into weeks?
Are you trying to get the week number from the wk/yyyy format? Since that will be a String and not a Date data type, the Date/Time functions won't work. Instead you could use a Test To Columns tool to split up the two parts and then convert each to a number, either with a Select tool or the ToNumber() function--In the attached workflow, I used a Select tool so that I could rename the columns at the same time.
I have two fields that are set as a string however we use them to input currency numbers into. (Example: $1,000.00 or ($2,000.00)). I tried using the ToNumber() function however that is not working. I'm assuming I can use the regex tool however I'm really not formula with that. Any suggestions would be great. Thanks Heather
Though when working with currencies, the '$' and comma characters will always cause the field to revert to a string, so you may have to trim or remove those before trying to do any calculations with that field. Then if your desired output is to be formatted as currency, you can transform your data back as the last step before output.
NickSm, Thanks for the response. I can't use trim as the same column could have $1,000 or ($1,000) so they have a different number of characters in front to be trimmed. My end result needs to be 1000.00 or -2000.00 so that it's an actual number that I can add to another amount field. I need to have the dollar sign, comma and parenthesis removed and leave the period so the number doesn't get change (example if period was removed: from $1,000.00 to 100000). This is why I assumed I would need to use the RegX tool, however I don't really know much about the tool. Thanks
NickSm, Thank you this was helpful. I got the ToNumber(replaceChar()) formula to work for the positive numbers however I cant figure out how to get it to work for the negative numbers because of the parenthesis (Example: ($2,000.00)). I created a example workflow to show you. Any suggestions on how to handle the numbers that need to be negative would be great.
You're on the right track - just to deal with the negative numbers you'll want to make sure you do a Replace formula for the '(' character to a '-' and then you can use the ToNumber just as you have it.
I am trying to run a workflow with an iterative macro. My input data has 4K records, and I want my workflow to run in batches of 10 or 100 or 500. But when using the iteration number and macro, it will run the first time correctly, and then iterate +1 by +1 each of the remaining records.
Hey @gfloresDiaz, it's first of all worth mentioning that the [Engine.IterationNumber] is 0-based and so you may want to offset this by +1. Looking at the way you have this set up, you're just adding 10 to the number of times the macro has ran (again starting at 0 due to the nature of the variable), so your first few runs will look like this in terms of the Filter's behaviour:
Which is why your workflow will just continue to run through each record, as you're only filtering one extra ID each iteration. If you want to batch each iteration into chunks of 10 as you say, you may want to create your [Count] by using something like:
Hope this helps, apologies in advance if I've read into this incorrectly but please do let us know if that's the case, perhaps with some additional detail/context, and we can continue to work on a solution!
I am trying to learn better how to utilize RegEx for pattern matching kind of processes, so I have attached an example of at least three ways you could handle this in Alteryx. Two of them using RegEx.
Yours is a bit more dynamic, although your's would eliminate a Hyphen or Parenthesis if needed for indicating a negative number, correct? And mine would still retain a Parenthesis which wouldn't convert to a number (realizing that neither are in the example from @mvangodung, but probably should be incorporated in the solution, right? )
Just to make the solution complete, here is the modified workflow with the three possible methods that I offered earlier...but accomodating for negative numbers (indicated by either a hyphen or parentheses).
If the field in question has a mix of letters and numbers it's likely formatted as a String or V_String type. You might be better off using a regular expression to parse this out to see if it contains a number.
Add a formula tool after your input and enter the expression that @MarqueeCrew provided (including your field name). Wrapping it with a Switch function will give you a True / False value rather than 0,-1.
I want [replacement] to show "True" when "40116486, 142698960" is in the "replacement order#" field it is currently showing False. I want "40116486, 142698960" to continue to show in the "replacement order#" field as they are for two valid order numbers. When I do parse it seems to remove one of the numbers which is not what I want.
If you use a SELECT tool to change the type from text to "DOUBLE", you'll get plenty of warning messages and all of your text will change to the value of 0. Numbers (values with 0-9 and a decimal) will be converted properly.
You cannot have two different datatypes in the same column. Either you have numbers stored as text, or you convert the field to a numeric value and you lose the text/words. It is not possible to "mix and match" datatypes in the same column.
However, for vlookups that is replicated through a Join tool. Ideally you'd want to make the field that you're doing the vlookup to match in datatype. I suggest changing both to strings, and then doing the vlookup. Whether the value is a number or a string won't matter.
c80f0f1006