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?

[nimit_b19]

@danilang Thanks for the help there. I can open the file now.

@grazitti_sapna The solution looks great. There's one complexity here though.

The incoming data in the 'Time Left' can be of the following forms:

• ss Seconds
• 1 Second
• mm Minutes
• 1 Minute
• hh Hours
• 1 Hour
• hh Hours mm Minutes
• hh Hours 1 Minute
• 1 Hour mm Minutes
• 1 Hour 1 Minute
• dd Days hh Hours mm Minutes
• 1 Day hh Hours mm Minutes
• 1 Day 1 Hour mm Minutes
• 1 Day 1 Hour 1 Minute
• etc.

Anything less than a minute will be sent in seconds, anything more than a minute will not have seconds. 

The maximum categorization will be in days. (No weeks/months/years)

How do I make the flow adapt to this schema?

[danilang · Accepted solution]

Hi @nimit_b19 

@grazitti_sapna has a good solution, but you have a lot of different formats to try and handle with one DateTime tool.  Also adding in the days counter means added complexity to build a DateTime field without a month or year.  Also, the Datetime tool will error if you have more than 31 days worth of elapsed time

This solution uses as series of Regex parse tools to extract each of the components individually, cleans the data and performs the total

Here are some sample results

Dan

[nimit_b19]

That works perfectly! Thanks for the solution @danilang.

Can you possibly tell me how you've used the Regex tool to separate out the numbers?

[danilang]

HI @nimit_b19 

Glad to help!

Each of the regex tools uses the same format

.*?(\d+) Day.*

.*? matches any character "." except line feeds 0 or times "*".  The "?" means to stop matching characters when when you hit the next group, which is

(\d+) "\d" matches any digit.  "+" means find the preceding item one or more times.  The brackets around \d+ signify that this is a group you want to return

" Day" means match any string that that is exactly " Day". Note the leading space. 

.*  matches any character "." except line feeds 0 or times "*".

The match is also case insensitive so it matches "day", "Day", "DAY" and any other variant

When you put it all together, it looks for any string of the form "# Day", anywhere in the string, where # represents one or more digits and it returns the #. 

The other Parse tool have the same format but replace Day with Hour, Minute and Second.  Each of them acts independently of the others, so together they will handle any possible combination of day, hours, minutes and seconds in any order

Dan