Hi All,
I am trying to prepare a JSON data using the following workflow
But cross tab is giving me some unwanted fields like shown below
Can you please help me understand why these files are being created?
Solved! Go to Solution.
Hi Adij,
These columns are being generated because they're part of the JSON output you're download.
I don't know exactly what you're downloading, but it looks like some system details are being given in your output.
Like Marquee says you could filter them out before cross-tabbing your data by filtering out anything that contains resourceRequests_
Okay. How would I do that? What expression should I use? I am very new to Alteryx.
Hi @adij0628,
To filter out the additional columns you can use a select tool to do it manually, a dynamic select tool to write a formula and do it dynamically or a macro to remove Null columns. (I'm assuming that the extra columns are Empty).
A lot of Alteryx Users have created their own macros to remove Null columns. I've attached Mine. It looks at the first 100 rows and if all are Null, then looks at the rest of the data for that column to determine if it's Null() all the way down...
Note: As the Cross-tab tool converts Null() to Empty, you'll need to use a Multi-field formula tool before the Remove Nulls Macro. The Multi-field formula tool will have the following formula: IIF(IsEmpty([_CurrentField_]),Null(),[_CurrentField_]).
Kane
I'm having this same issue that is causing an issue with bulk loading process. Null dates are being converted to empty dates after passing through a crosstab. Empty dates cause an error when being bulk loaded into Date columns. I need to check all columns for any non-UTC-8 characters so I Transpose -> Regex --> Crosstab to put the record back together. Any columns with null values get transformed to empty by the Crosstab. Since this is in a macro the date columns could anywhere. Seeking a solution.