We hope you enjoyed last week's challenge. The solution has been posted here. For the second challenge lets look at removing characters and splitting data into columns based on delimiters.
Many products will export textual data with delimiters such as quotes. This is done so that strings can contain delimiters or control characters within them. Having more than one type of delimiter can be hard for ETL programs to interpret. In the input text file, there are two different delimiters (double quotes, single quotes) and they surround different data types.
Use Alteryx to strip out the delimiters as superfluous and format the data as represented in the output.
You may notice that we have started classifying the exercises into beginner, Intermediate and advanced. This classification is used by Alteryx internally to sequence exercises as users advance.
Update 11/23/2015:
The solution has been uploaded.
... View more
Hello Community Members,
A solution to last week’s challenge can be found here.
This challenge was submitted by Yash Thakkar. We appreciate your contribution @yash_thakkar!
Some months mark pivotal moments for students and job seekers worldwide, especially when exploring the H-1B job market. The H-1B visa program allows US employers to temporarily hire foreign workers in specialty occupations requiring a combination of highly specialized knowledge and a bachelor's degree or higher in a relevant field, or its equivalent.
The dataset provided includes fiscal year 2024 data on employers who have submitted petitions to employ H-1B workers.
Your tasks are:
Identify the top 10 companies that submitted the most H-1B petitions in 2024.
Determine the top 10 industries with the highest demand (number of total petitions submitted) for H-1B workers in 2024.
Hints:
Exclude companies with missing industry data (Industry (NAICS) Code).
Focus only on approved petitions as follows:
Initial Approval (first-time H-1B employment)
Continuing Approval (extensions or changes for existing H-1B employees)
Happy solving!
Sources:
https://www.uscis.gov/tools/reports-and-studies/h-1b-employer-data-hub
https://www.uscis.gov/tools/reports-and-studies/h-1b-employer-data-hub/understanding-our-h-1b-employer-data-hub
The Academy Team
... View more
The link to last week’s challenge (challenge #24) is HERE.
As we have said before, restructuring poorly formatted data is one of the most common Alteryx use cases. This week’s challenge is another real world example of a data problem faced by one of our customers.
Use Case: A credit card company’s customer Data is structured so that each row of data contains the merchants each customer has visited in a given week. The credit card company is wanting to understand the correlation of merchants visited by customer. For example: if a customer visit 7-11 what other stores do they have a high propensity to visit?
Objective: Restructure the data into rows that pair merchants together by customer.
... View more
Here is a new challenge for this week. The link to the solution for last week’s challenge is HERE.
The use case:
We received some text data and that includes an embedded line-feed character.
The objective is to remove the new line character, convert the date-time string to a date-time formatted field and then do some renaming per the sample output.
Good luck, I look forward to your feedback.
... View more
A solution to last week's challenge can be found here!
The input file below contains data on the toll stops of different vehicles (identified by plate_id). The Text Input contains the toll for driving a Segment between two toll booths. For example, it costs $2.25 to drive between toll booth 1 and tool booth 2 (1,2). Plates are tracked to identify the toll booths they pass and the time between toll boths.
Calculate the total tolls and time spent on the road per [plate_id].
Source: GIPHY
... View more
