Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

How to load multiple json files into a single table?

iiyoob
6 - Meteoroid

I have a number of identically structured json files.  I'd like to load them into a single table such that each file is a row in the table.  The table column names should be derived from the 'key' of the json files.

 

For a single file, I am able to use a simple crosstab and it works.  However, when I try for multiple files using a Directory + Dynamic Input, followed by CrossTab, then only 1 row is created with comma separated values.  What I need is for these to be in separate rows, not concatenated into a single row.

 

Any help is appreciated.

4 REPLIES 4
SeanAdams
17 - Castor
17 - Castor

Hey @iiyoob,

 

Firstly, welcome to the community and congratulations on your first post.

 

Are you able to attach a simplified version of your workflow, and 2 or 3 of your JSON files to this thread, along with a quick table that shows the outcome you are looking for?   That would really help to get you to a good solution as quick as possible (very similar to the way that the weekly challenge team do this).   

 

Hope you have a good Sunday.

Sean

iiyoob
6 - Meteoroid

Hi @SeanAdams,

 

Below are two sample json files.  

 

auto1.json

{
  "AutomobileType": "tesla3",
  "LaunchIndex": 0,
  "ProductCodes": [],
  "PurchaseTime": "2017-02-20 23:35:27+00:00",
  "NetworkInterfaces": [
    {
      "Attachment": {
        "AttachTime": "2017-02-20 23:35:27+00:00",
        "AttachmentId": "eni-attach-3u8fns94",
      },
      "NetworkInterfaceId": "eni-4n4c9emf",
      "Status": "in-use",
    }
  ]
}

auto2.json

{
  "AutomobileType": "teslaS",
  "LaunchIndex": 0,
  "ProductCodes": [],
  "PurchaseTime": "2017-02-21 21:35:27+00:00",
  "NetworkInterfaces": [
    {
      "Attachment": {
        "AttachTime": "2017-02-20 23:35:27+00:00",
        "AttachmentId": "eni-attach-j4j2b4j4",
      },
      "NetworkInterfaceId": "eni-9dj38m33",
      "Status": "in-use",
    }
  ]
}

 

 

I am able to get what I want using the 'jsonFileJoin.yxmd' workflow, but I need to be able to get the same output using the 'jsonFolderJoin.yxmd' workflow because I have thousands of such json files.

 

 

Thanks

-Ilyas

SophiaF
Alteryx
Alteryx

In order to get each JSON file on each row, you need to set a field to GroupBy in the Crosstab - ideally this would be some type of File_Id field. Since you can't pass any records through the Dynamic Input (other than the actual data that comes in), there are 2 ways you could do this:

 

1. Use a multi-row formula tool after the Dynamic Input to create a field that groups the appropriate records together by File

2. Use a batch macro to read in each file

 

I personally think the Batch Macro approach is easier (and less error-prone), as you can use the constant [Engine.IterationNumber] to easily make a File_ID field (this is done with the following expression in the Formula tool:

1+[Engine.IterationNumber]

GcjX9O

 

You can then insert this macro instead of the Dynamic Input, and use the newly created FileID field as your Groupby field in the Crosstab

 

haIxyT

 

Sample workflow attached

Sophia Fraticelli
Senior Solutions Architect
Alteryx, Inc.
iiyoob
6 - Meteoroid

That works @SophiaF !  I hadn't used batch macros before, so that's another thing I've learnt.  Thanks!

Labels