How to avoid Data Truncation

[nimit_b19]

I have data coming in through an API in JSON format. I want to store this data in a SQL server in a crosstab format.

Following is the flow I have created to achieve this:

I am able to successfully write the data in the required format to the SQL Tables.

However, some of the data is getting truncated in the process.

I have a field called 'Time Left' which ideally should be in seconds (float) format but the API is sending it in " xx Hours yy Minutes zz Seconds " format. I tried to change this to float, I get only the first number and the rest of the data is lost. I tried converting it to date time and still did not get the desired output.

This is what the API is sending:

This is what happens after 'Select' tool:

Is there a way I can convert the incoming data to seconds in Alteryx and store it in float format?

[grazitti_sapna]

Hi @nimit_b19 ,

I guess you need to apply formula to convert hours and minutes into seconds first.

[nimit_b19]

Yes, the initial type is String but I want to convert it to seconds (float) to later process the data in Tableau.

[grazitti_sapna]

Hi @nimit_b19 ,

Please find the solution.

Thanks.

EDIT: this workflow is for date format like 3 Hours and 45 Minutes in other case we need to parse the digits from the string and then calculate them with the formula mentioned in the workflow.

[nimit_b19]

Thanks for the solution! However, I'm not able to open this in my copy of Alteryx since you're on a newer version than me. I can't update since I'm on an Enterprise license.

[grazitti_sapna · Accepted solution]

@nimit_b19  no problem I will attach the image of the workflow along with this post.

[nimit_b19]

Thanks for the screenshot!

Can you possibly also paste the screenshots for each configuration?

Many thanks in advance.

[danilang]

Hi @nimit_b19 

You can change the version @grazitti_sapna's  workflow by opening the .yxmd in notepad and changing the 2019.2 in second line to match which ever version of alteryx you're using.  Look in Help->About to find your version

<?xml version="1.0"?>
<AlteryxDocument yxmdVer="2019.2">

Dan

[nimit_b19]

Hi Dan,

I tried what you suggested but this is what I see when I open the file using notepad:

[danilang]

hi @nimit_b19 

You opened the .yxzp package which is renamed zip file.  Find the timeleft.yxmd workflow that was extracted.  It should be in a subdirectory call "timeleft" 

Dan