Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Input data with multiple format date in one column

NurulAtikah
8 - Asteroid

Hi everyone,

 

I have a question regarding input data and data type, currently, I have one column name "Date" in excel and it consists of 2 formats of data one in date and one in-text format. When I input the excel file in alteryx, alteryx detect as Date format, and date in text format become null. Is there any possible way to make the value date appear (not null)?

 

NurulAtikah_0-1603765634294.png

 

Thank you

 

11 REPLIES 11
atcodedog05
22 - Nova
22 - Nova

Hi @NurulAtikah 

 

Here is a formula for the task.

IF !IsEmpty(DateTimeParse([Date],"%d/%m/%y")) 
THEN DateTimeParse([Date],"%d/%m/%y") 
ELSEIF !IsEmpty(DateTimeParse([Date],"%d-%b-%y"))  
THEN DateTimeParse([Date],"%d-%b-%y") 
ELSE Null() ENDIF

Output:

atcodedog05_0-1603769026540.png

Workflow:

atcodedog05_1-1603769043498.png

Hope this helps 🙂


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

NurulAtikah
8 - Asteroid

Hi @atcodedog05 ,

 

Thank you for your response.

However, the problem is when I input data using the input tool, the date which in the format string (20-Feb-2020) becomes null so I can't convert as your suggestions.

 

Thank you.

atcodedog05
22 - Nova
22 - Nova

Hi @NurulAtikah 

 

Can you provide a sample file.

grazitti_sapna
17 - Castor

Hi @NurulAtikah . can you share your input file, so that I can test it? One thing I can think of to solve this issue is to convert your date field into string using select tool.

grazitti_sapna_0-1603772519953.png

 

I believe you will be able to read all the data then after that you can apply formula tool and convert the dates into alteryx format and change the data type into date by copying the values into another column or can replace the datatype of the same column using select tool. I hope this will help you.

 

Thanks.

Sapna Gupta
NurulAtikah
8 - Asteroid

My file in format .xlsb however, I can share with you a sample in .xlsx format and you may change to .xlsb format because the one that has the problem is in .xlsb format.

 

 

My data in excel

NurulAtikah_1-1603779271004.png

 

And after input using input data, it becomes null

NurulAtikah_2-1603779327761.png

 

 

 

 

 

atcodedog05
22 - Nova
22 - Nova

Hi @NurulAtikah 

 

Sorry can you please share the .xlsb file format. I having some issues converting.

I will be able to help you much better you share that file.

 

There was no issue reading .xlsx file

atcodedog05
22 - Nova
22 - Nova

Hi @NurulAtikah 

 

I was able to replicate your issue on .xlsb file.

 

Output:

atcodedog05_0-1603780313870.png

Seems like .xlsb file is sending meta data of columns datatype hence its running into a issue.

 

Let me look for a work around.

 

atcodedog05
22 - Nova
22 - Nova

Hi @NurulAtikah 

 

Kudos i was able to find a workaround.

 

While reading the file from input tool set first row has data like below Input tool config. Then it will take column as a string.

atcodedog05_0-1603780519174.png

Output:

atcodedog05_1-1603780569807.png

Workflow:

atcodedog05_2-1603780598109.png

Hope this helps 🙂


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

grazitti_sapna
17 - Castor

Hi @NurulAtikah , my approach to the problem is also somehow similar to @atcodedog05  as I could not find any other possible way to tackle this.

grazitti_sapna_0-1603781180819.png

 

Might be a slight difference.

 

Thanks.

Sapna Gupta
Labels