We're excited to announce that we'll be partnering with Credly starting October 19th - see what this means and read the announcement blog here!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Parsing JSON, handling nested nodes

SeanReadFord
6 - Meteoroid

I’ve got a JSON to Excel definition working, but I’m stuck at enhancing it. The JSON file is an API response that contains a list of problems; it contains a few “header” fields (# of problems returned for example), then a list of problems.  It’s working to the point where I can pull out just the problems and put them to an Excel file, one problem per row.  So far that’s good; but the problems can have a list of comments within them.  At this point it’s concatenating the four fields relating to the comment (an ID, timestamp of entry, userID who entered it, and comment text), and concatenating multiple comments; that all goes into one “comments” column in the problem’s row.  I’d like to refine this a bit; I want to omit the comment ID, I’d like to convert the timestamp to something readable (1629212755561 to 8/17/2021 11:05AM, for example).  I’m fine with the timestamp/userID/text concatenated & multiple comments concatenated together in one “comments” cell for that problem’s row.

 

Any suggestions? I've attached the alteryx definition I'm running and the test JSON data file (had to rename it with .txt so the community would let me attach it)

4 REPLIES 4
Drussek
9 - Comet

Not sure if I understood you correctly.

Every comment is now in separate row. You can concatenate them as you wish.

SeanReadFord
6 - Meteoroid

Thank you, this is a great start!  I see the comments fields split out into their individual pieces, and you did the timestamp conversion for me 🙂  Ultimately I'd like to have the last three rows (three comments related to the same problem) as just one row, with the three comments concatenated together; so there'd be a comments field containing (given this data):

2021-08-17 20:03:22 // sread25 // comment, with, commas - test 8/17/2021 4:03PM /// 2021-08-17 15:05:55 // sread25 // 2nd test comment @ 8/17/2021 11:05AM  /// 2021-08-17 12:09:28 // sread25 // test comment @ 8/17/2021 8:09AM

SeanReadFord
6 - Meteoroid

Got it!  Attaching a revised YMXD file, for the curious.  I added a formula to concatenate the three fields of interest (author, formatted timestamp, and comment text), then a Summarize tool to concatenate the three rows into a single cell before the JOIN/UNION.  Drussek, many thanks for this!

SeanReadFord
6 - Meteoroid

One glitch I've found.  If the input file contains only problems that have *no*comments, the script fails (specifically at the formula to create the DateTime field from the timestamp).  The 'comments' node is there, so it passes the filter for RecordNum2=comments, but the list of comments is empty so the fields within there (author, timestamp, content) don't exist. There's a "totalCount" field within the "recentComments" node, that's 0 if there are no comments, and I've been trying to add a filter using that but so far have had no luck.  Any assistance would be appreciated. 

Labels