We've recently made an accessibility improvement to the community and therefore posts without any content are no longer allowed. Please use the spoiler feature or add a short message in the message body in order to submit your weekly challenge.
We’re aware of an intermittent issue with our My Alteryx login and are actively working to have the issue corrected. If you run into an error when logging in, please try clearing all cookies or accessing the community on a different browser. Thank you for your patience!

Weekly Challenge

Solve the challenge, share your solution and summit the ranks of our Community!

Also available in | Français | Português | Español | 日本語
IDEAS WANTED

We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!

Submit Feedback

Challenge #2: Preparing Delimited Data

Inactive User
Not applicable

@TaraM  thank you, the article is very useful!

KOBoyle
11 - Bolide

I don't see how this solution retains delimiters or control characters that are present within a field.  If I change the first input from "Mary had a little lamb whose fleece was white as snow" to "Macy's, Inc. had a little lamb whose fleece was white as snow", where an apostrophe (text qualifier delimiter) and comma (field separator delimiter) are present in the text, the output is not correct.  Can someone clarify how to implement a comparable solution when the delimiters are not superfluous, and are used for their intended purpose?  Does Alteryx make a distintion between what spreadsheet software refers to as text qualifiers and field separators?  Thanks.

JoeM
Alteryx Alumni (Retired)

@KOBoyle, that's a great thought. In your example, where we transform the underlying data value: "Mary had a little lamb whose fleece was white as snow" to "Macy's, Inc. had a little lamb whose fleece was white as snow", the solution will fail. However, there are some options in the text to columns tool to help with cases like this. In the advanced options, we can choose when to ignore the specified delimiter when it is in 1) quotes, 2) single quotes 3) parentheses and 4) brackets. These options essentially operate as text qualifiers via the text to columns tool. If you reconfigure your solution in the text to column tool to appear as the following, your example will work:

challenge2.png

Also, note that this functionality is available for certain file types in the input data tool. For example, if I import a .txt file, I will have the following as a configuration option:

challenge2_2.png

KOBoyle
11 - Bolide

That is what I was looking for.  Thank you for the quick reply.

kconner
6 - Meteoroid

Great exercise. I really like these challenges to help learn the tool and get familiar with its abilities.

My solution is below. Basically I ran Text to columns on the ',', then stripped the quotes from the two fields. Converted the date and then selected the values for the final display.

 

solution displaysolution display

mceleavey
17 - Castor
17 - Castor

A simple solution:

Spoiler
Solution.PNG
I used the Text to Columns tool to seperate on commas, ensure "Ignore delimeters in quotes" is checked. This will ignore the commas in the text.
Then simply remove the quotes and convert the date.


Bulien

Max06270
7 - Meteor

Pretty simple, thank you for the challenge!

 

Spoiler
Capture.PNG
KatieA
Alteryx Alumni (Retired)

Solution attached!

SeanAdams
17 - Castor
17 - Castor

Thank you for the exercise - mostly the same as the other responders except:

Spoiler
- Used the new Alteryx 11 custom date capability in the DateTime component just to see how it works
- Used select tools throughout to remove unused data at each step (not needed for 2 rows, but good habit
- Added a simple tester to do a field-for-field check on the results vs. expectations
JoshK_dup_167
6 - Meteoroid

Going for gold.