API Downloading and Transforming Data

[BJackson56]

I'm trying to download CES data from FREDs via API. I've used this exact workflow on all my other extracts, but for some reason with CES data, i'm not able to get all my dates and data on one row. After I've made a join to give all the survey's Titles instead of Series ID's, my next Cross Tab creates 3 sets of date. Again, this is the only time it's doing this, so thinking maybe something with this data, but why can I not get the values to show on the same data row?

 

 Why am I getting 3 rows for date, containing 1, 2, and 3, sets of concatenated dates? There are several columns that have data in 2nd row, and only a handful with data in the 3 row of dates. I've deleted and reconfigured the Cross Tab tool and still same results...Can anyone save my sanity!?

[simonaubert_bd]

Hello @BJackson56 
I doubt this comes from API ;) mostly sure it's one of the crosstab. Can you show the data after the text to column?

[BJackson56]

 

And then just in case, here is the post filter data. 

 

[simonaubert_bd]

Yes @BJackson56  So I would accuse one of the 2 crosstable. We know what's after the second... Can we have what's after the first crosstab?

[BJackson56]

 

[apathetichell]

if you can post some sample data that would help - you have date in one of your crosstabs as a value in concatenate mode.

[BJackson56]

Sure! Here is what is supposed to be a Rolling 12 month view, really only showing 4 due to the multiple dates coming through. You can see that each month now has 3 rows of dates. Most is in the first, several in the 2nd and only a few in the 3rd row. 

[apathetichell]

can you change value to a number. replace nulls with 0 and then swap from concatenate to sum. 

Next questions - what version of Alteryx are you using - and is AMP on?

[BJackson56]

Designer x64. I convert the values to int64 in the select tool following the 2nd cross tab. There isn't an option to switch from concatenate to sum. Just 'Concatenate' or 'First' or 'Last' as far as Methods for Aggregating Values, in the cross tab tool. Not sure what AMP is, so I'm going to say no.

[apathetichell]

What version of designer64? amp is a mulitthreaded run module (bottom of the runtime screen in the workflow configuration menu) it can be " unique" with how it processes things.

Can you a) remind me which specific endpoint in fred you are getting data from (in case I get some free time and decide to rebuild end to end) and b) where things are showing up in your flow? Can you convert [Value] to int prior to tool 554 - then in tool 554 swap from concateate to sum?