Challenge #114: Think Like a CSE...Remove the extra delimiter from the JSON output

A solution to last week's challenge can be found here!

We are thrilled to present another Challenge from our “Think like a CSE” series, brought to you by our fearless team of Customer Support Engineers. Each month, the Customer Support team will ask Community members to “think like a CSE” to try to resolve a case that was inspired by real-life issues encountered by Alteryx users like you! This month we present the case of the incorrect JSON formatting.

Below, we’ve provided the information that was initially available to the Customer Support Engineer who resolved the case. It’s up to you to use this information to put a solution together for yourself.

The Case: A co-worker is trying to build an app that requires a JSON file as an input. He has created a workflow in Designer to produce this JSON file. The problem? The .JSON file is created but, after opening the file, he notices that it contains backslashes ‘\’ that should not be there (shown in fuschia below). For example:

[

{

"JSON": "{\"Record\":[{\"email\":\"ThinkLikeaCSE@alteryx.com\",\"mobile\":\"1-800-ALT-ERYX\",\"category\":\"Active\",\"cityPreference\":\"Irvine\",\

...

}

]

He cannot use this file to build his app, as it is not the proper format. Even more puzzling? When he reads in the .JSON file in Designer, the backslashes do not appear. What’s going on?

Your Goal: Create an output without the extra delimiter ‘\’.

Asset Description: The co-worker is desperate and has shared the workflow he uses, which includes a workflow (included in the start file).

Spoiler

(view in My Videos)

challenge_114_start_file.yxmd

challenge_114_solution.yxmd

Developer

Advanced

Intermediate

Data Preparation

Transform

Accepted answers

All comments

LordNeilLord

This was easier than I thought it would be...

Spoiler

With a little help from the awesome community, I did a search and found this post: https://community.alteryx.com/t5/Data-Sources/Writing-JSON-Files-Wrong-Syntax/td-p/10421

Which gave the answer!

challenge_114_LNL.yxmd

bdaniels

We've run into similar problems,

Spoiler

non-delimited .csv's solve everything.
Screen Shot 2018-04-30 at 10.36.05 AM.png

challenge_114_start_file.yxmd

kcgreen

I had no clue on this one but easily found a solution on the community website.

Spoiler

challenge_114_JSON.yxmd

mmongeon

Here is my "solution"...

Let me know if I messed it up, and I'll try another approach. (EDIT: I see other people used the same technique, so I feel ok now)

Spoiler

Feels like cheating, but I just changed the output file type to Comma-Delimited Text Files (*.csv), it seems to produce the desired output...

challenge_114_mongeon_complete.yxmd

leesin

challenge_114_start_file.yxmd

aikennedy

challenge_114_aik.yxmd

ShshankChawathay

Solution

challenge_114_start_file.yxmd

patrick_digan

Spoiler

challenge_114_start_file.yxmd

MMehdi

Challenge #114 done

Spoiler

Workflow
Works either by writing as HTML before extracting content or by just writing to CSV without delimiters nor header extraction

challenge_114_resolved_by_MM.yxmd

kennydso

challenge_114_KennyDsouza.yxmd

unknown

challenge_114_ryan_lambert.yxmd

JimPer

Here it is !

Spoiler

challenge_114_JimPer.yxmd

ckings

Spoiler

Change the output tool to .CSV and the delimiter to no delimiter

challenge_114_CAK.yxzp

NicoleJohnson

My solution!

Spoiler

Same as the others, I eventually found some info on the site about the extra characters in JSON output resolving if you output to CSV... however, this might be a stupid question, but couldn't you avoid this whole thing by just outputting your original data direct to JSON, without the Transpose/formulas/JSON Build tools in between? Looks largely similar in output, but as I'm not super familiar with JSON, I'm unclear whether there is an advantage/purpose for having all records combined (as in the first example) or if a separate record for each would be acceptable. Any insight from the JSON experts out there on why you would do this one way vs. the other would be great!!

Cheers,

challenge_114_NicoleJohnson.yxmd

ggruccio

This one had me stumped for a bit!

Spoiler

Not really much of a spoiler, but I had to search community for an approach to this. I knew there had to be something related to the output tool. Found an article that addressed this about 3 years ago. Just another example of how great the Alteryx community is as a resource and how we may not know everything ourselves, but can solve with collaboration!

challenge_114_gg_finish.yxmd

david_fetters

challenge_114_solution DF.yxmd

Spoiler

Spoiler

If you compare the 2 outputs:

NotworkingNotworking1

The 'Notworking' defines a Record object with an array of 2 while the 'Notworking1' does not have the Record object at all. Inside the object, we can declare any number of properties using "name" : "value" paring, separated by commas. In the 'Notworking example' if I wanted to access the information, I would just call the record object while in the Notworking example, it would be harder for me to call the object as there isn't one.

petgrap

On the designer core exam that I recently passed, there was a question on \0 delimiter. Not familiar with it, I ask during a webex training this week to demo the concept. I did not see the delimiter option with the JSON output (no experience with JSON).

Tried to use it in the formula did not work. So I turned to the community topic to solve the challenge and learned a new trick of using CSV output but saving the file with a different extension.

I love these challenges. I am always learning something new which eventually becomes useful!!!

challenge_114_start_file-PP.yxmd

dominiklz

Spoiler

quick and dirty fix, just write in another raw text format ensuring to leave out all noise from that format, I picked .csv without delimiters and fieldnames

wow, that was a fast one, curious to browse through other peoples solutions

challenge_114_Dominik.yxmd

kartheek31

My solution is same as everyone, even I got this knowledge from the community

Thanks

Kartheek

challenge_114_solution_kartheek.yxmd

Vijuzy

After reading challenge I thought I need to spend more time on this, then realized not all problems are complex as it sound

Spoiler

Just change the file format, why to format already formatted data...

challenge_114_VJ.yxmd

estherb47

This one seems deceptively easy, but the solution already in the community supports it, so I'll just run with an easy challenge

Spoiler

Change the output type to CSV, no delimiter. That eliminates the possibility of anything wonky being inserted.

challenge_114_EHB_Completed.yxmd

GalynaSkamrova

My solution is pretty much the same

Spoiler

challenge_114_Solution_GS.yxmd

terry10

challenge_114_terry10.yxmd

SeanAdams

Solved this like many of the folk above - was glad to see @NicoleJohnson 's solution

Spoiler

I just changed the file type to CSV and that gets rid of the \ issue - was glad to see Nicole's post about just using the JSON output up front without all the other complexity.

challenge_114_SeanAdamsSolution.yxmd

StevenBetts

Thank goodness for the community on this one!

Spoiler

challenge_114_start_file_SMB.yxmd

DawnR

Done!

challenge_114_DawnR_Completed.yxmd

jasperlch

Solution attached. Thanks!

challenge_114_JL.yxmd

mceleavey

This made me chuckle as I recently had this exact problem with a workflow I developed for an fs company. After much forehead table action I stumbled upon a post in the community (sorry, can't find it to give due credit) which gave me the simplest of solutions: