This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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)
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):
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!
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.