Date Conversions

[achadaga]

How do I convert a date which is in Unix format in alteryx ?. I used the formula tool ([date]/(60*60*24)+"1/1/1970") the way we use in excel, but I'm getting error.  Tried using all the date conversion tool in alteryx but I am getting null values as output.

[DylanB · Accepted solution]

The issue that you are having is that you're trying to add a numeric value ([date], if in Unix time, is just a number). You cannot add across incompatible types in Alteryx, but you can use conversion functions to do this. I actually cannot see what you are trying to do, as your dataset is not accompanying your workflow, but I would imagine something like 

DateTimeAdd('1970-01-01',[date],"seconds") 

should get you the current time in seconds. Then you can use dateTime functions to get it into your desired format.

Let me know if this doesn't work.

[achadaga]

Thanks for the response Dylan. I was able to get the data from API into alteryx as desired. But, the total rows i am able to import are just 1000. In the actual dataset there are 50000 rows. Is it a limitation in alteryx ?

[DylanB]

I'm able to import much more than the 1000 records. I stopped mine at around 9 GBs, and I was only 20% done. There are two ways to view data within a workflow. One is through the browse everywhere feature in the "Results" section; this will give you a limited excerpt (min displays 1035). The other is through a browse tool and through the results section of that. This should give all of the results.

[achadaga]

Hi Dylan,

               When i run the workflow, it does fetch 15.2 GB of the records, but if you transform that data into the actual rows, it amounts to just 1001 rows. The actual data has 50000 rows. I need those many rows in order for me to run the model

[DylanB]

It looks like the limit for your api is 1000 and that you have to specify a different limit in the call:

http://dev.socrata.com/docs/paging.html

You can do that, but keep in mind that if 1000 records took 15.2 GB, you're looking at downloading over 750GB of data. 

On a different note, your data had more fields than I saw in the screenshot you posted a while back, so you should changed 

[JSON_ValueString]+tostring([JSON_ValueInt])+tostring([JSON_ValueFloat])

to

[JSON_ValueString]+tostring([JSON_ValueInt])+tostring([JSON_ValueFloat])+tostring([JSON_ValueBool])

if you want the data in that field.