Challenge #139: Spooky API Connection

Last week's solution can be found here!

Who's ready for halloween?

Ok, maybe the API isn't inherently spooky - it's one of the simpler ones out there! What is 'spooky' about it is understanding candy consumption of children this week!

For any parents out there, you might be wondering what the nutrition stats look like when your child dumps all his/her goodies out the pillow case and marks short work of the sweets. More often than not, the small distributed candies do not have nutritional facts to review. Alteryx to the rescue! Using USDA API, try to calculate how many calories we've collected in out bag.

1) Sign up for the USDA NDB API (sub 30 sec) https://ndb.nal.usda.gov/ndb/doc/index
2) Read up on the documentaion https://ndb.nal.usda.gov/ndb/doc/apilist/API-FOOD-REPORTV2.md
3) Using the Bag Data (Candy x Qty), lookup the NBD IDs and create ONE and only ONE request (use basic response type).

Hints for solution:
1) We only filtered 'nutrient.x.measures' out and just kept
2) Kcal is a calorie

challenge_139_start_file.yxmd

Parse

Join

Preparation

Advanced

Data Analysis

Intermediate

Data Preparation

Transform

Accepted answers

All comments

patrick_digan

Spoiler

@JoeM My solution assumes that Atomic Fireballs = Atmoic Fireballs. I've never tried the latter.
At 2,500 calories a day, it looks like I could be sustained for almost 2 weeks.

challenge_139_start_file.yxmd

cgoodman

Spent too long parsing the json data out.

Spoiler

challenge_139_solution(upload version).yxmd

cgoodman

I get the same answer as @patrick_digan if I amend the spelling to Atomic Fireballs.

kcgreen

My answer doesn't match the given solution but I get the same as Patrick_Digin.

Spoiler

I also made the assumption that Atmoic Fireballs were the same as Atomic Fireballs

challenge_139_API.yxzp

augustj

I feel like I may have some extra steps but I came up with the same answer.

challenge_139_solution.yxmd

JoeM

The fat-fingered data entry accounts for our dependencies! Thanks @patrick_digan for always keeping me on the straight and narrow! The start file has been updated.

kat

Spoiler

I got a slightly different calorie count as I took into account the pieces that made up the 100g of candy, instead of assuming that the given quantity was in increments of 100g.
Really cool idea for a challenge!

Challenge #139.PNG

Challenge #139.yxmd

jasperlch

Solution attached

challenge_139_JL.yxmd

JEBS

My solution assumes that Atmoic Fireballs =! Atomic Fireballs

Jeppe

challenge_139_start_file_solution.yxmd

sprodger

challenge_139solution.yxmd

CHarrison

Cadbury's Heroes all Halloween for me!

Challenge 139.yxmd

OliverW

Hi all,

here is my Solution, just quickly built this together, so there is probably room to improve it. I was lazy about parsing the JSON really, so I just used a filter looking for kcal and then, knowing that the actual value is in the next row, just used a RecordID +1 Formula to create a "RecordID Kcal" and then use a Join tool with the original data coming back from the Download tool to get the Kcal value, its a common technique I use usually when building Website Connectors with Alteryx.

Cheers

Olli

challenge_139_start_file.yxmd

paul_f

My first ever Weekly Challenge attempt

Alteryx-Weekly-Challenge-139.yxmd

ponraj

Here is my solution!

Spoiler

Workflow & Results

challenge_139_solution.yxmd

danilang

That's one big, bloated tummy!

Spoiler

I took advantage of the fact that the USDA provides XML data and use the XML parse tools to find what I needed

Dan

challenge_139_solution_DL.yxmd

tan_mingh_ui

First submission, not the most elegant though!

challenge_139_start_file.yxmd

CharlieS

I also changed the spelling.

Challenge_0139-CharlieS_Solution.yxmd

Thableaus

Spoiler

Cool exercise!

Here's my solution.

Challenge_139_Thableaus_Solution.yxmd

Ladarthure

Nice challenge, nice to use API

challenge_139_solution.yxmd

TylerNa

Haven't had a chance to use an API in Alteryx before so that was pretty sweet!

Spoiler

WC139 Spooky Candy TN.yxmd

George_Walker

Spoiler

Here's my solution

challenge_139_solution.yxmd

StevenBetts

Here's my solution. Took longer than I'd care to admit!

Spoiler

challenge_139_start_file_SMB.yxmd

GarthG

Didn't want to know what's in there!

challenge_139_GG.yxmd

ZenonH

First time using an API, this was really fun.

Spoiler

challenge_139_ZH_Sol.yxmd

Saravanakumar

Here is my solution

challenge_139_Final_file.yxmd

BigRuss

Here's my solution

Challenge 139 Solution - RH.yxmd

Verakso

This was fun, and more difficult that I first anticipated.

I too had to spend some time om the parsing, but i the end I came pretty close.

I did not match the result provided, but I can see, that others has questioned whether Atmoic Fireballs is the same as Atomic Fireball.

Since the bag only mentions Fireballs, I assumed they where the same, but oddly enough, it I remove the calorie count for the "Atmoic" Fireballs, then my calorie of the bag is equivalent with the provided solution.

Spoiler

So here it is, it's not pretty, but I came close enough I think...

My solution for this weekly challenge

Until next week...

Happy Halloween

challenge_139_verakso.yxmd

datamonkey

Great way to learn connecting via REST API!

challenge_139_my_solution.yxmd

JoeS

Love the challenge.

After reading the documentation and the fact that you could send in multiple candies in one request I started to go down that root. I then realised that meant the JSON parsing would be more involved, so switched to sending 5 requests and built quite a tidy solution (well at least I think so!)