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

Date Format

areeba
8 - Asteroid

Hi

I have one requirement in which I have few date columns whose format is '2020-12-31' and data type is STRING but I want to parse them into '31-12-2020' format as DATEdatatype . I am using Multi Field Formula for this but not able to write the correct expression. Can you please help me ?

For eg:

Input:

Contract Date     Hiring date

2020-12-31          2020-8-3

 

I need to have Output:

Contract Date    Hiring Date

31-12-2020         3-8-2020

 

 

Also I have one column called Value  which has following data :

 

Input:

Value

1.1111

0.8889

0.75

0.0278

 

I need to have output as

Value

1,1111

0,8889

0,75

0,0278

This value has DOUBLE datatype.

 

Thanks

Areeba

16 REPLIES 16
PhilipMannering
16 - Nebula
16 - Nebula

Hi @areeba 

 

Bear in mind that if you want Alteryx to recognize dates as a date data type, then it has to be in the yyyy-mm-dd format. See solution attached,

PhilipMannering_0-1605875723228.png

 

Qiu
20 - Arcturus
20 - Arcturus

@areeba 
@PhilipMannering answered your first one.

Qiu_0-1605876256480.png

 

areeba
8 - Asteroid

Hi

Thanks for quick reply but this solution giving error "Type mismatch. Number provided where a string is required". Its the same error which I was having. Can you tell what I am missing?

Also I see still a dot in Value field (1.111)instead of comma (1,111)

 

Regards

Areeba 

areeba
8 - Asteroid

Hi @Qiu 

Thanks for the solution but the thing is i cannot change the datatype of Value which is DOUBLEto V_WSTRING because for further calculations i need to round the value. The Replace function is applicable to string values.

Can you suggest a solution in which I do not have to change the datatype and get the comma value(1,111)

 

Regards

Areeba

PhilipMannering
16 - Nebula
16 - Nebula

Hey,

 

If you're converting the data type you need to save the new value as a new field.

PhilipMannering_0-1605877025919.png

Just to reiterate, if you have your dates in a non yyyy-mm-dd format and numbers using a comma instead of a fullstop, these values can only be stored as strings. And in the formula tool you need to save as a new field to change the data type. Solution is attached to help.

 

 

AkimasaKajitani
17 - Castor
17 - Castor

Hi @areeba 

 

Answer 1:

 

"2020-12-31" is Alteryx standard date type format.

But "2020-8-3" is not Alteryx standard date type format.
So you have to change type from "String" to "Date" format by DataTime tool or Formula tool using DateTimeParse function.

And then you can change the required format by DataTime tool or Formula tool using DateTimeFormat function.

 

OR

 

You can use Text To Columns tool. you may change the type to String and split at delimiter is "-".

And then you may concatenate the 3 columns by formula tool.

 

クリップボード一時ファイル04.png

 

Alteryx Designer only treats Alteryx standard formats("yyyy-mm-dd") as date types.

Others are all "String" type.

 

Answer 2:

It seem the same that you provided. But you want to change the type  to Double?

It is possible with the Select tool.

 

What looked like a dot was a comma, my apologies.

 

 

Qiu
20 - Arcturus
20 - Arcturus

@areeba 
The comma will force the field to be string. so we have to have new column for this.

areeba
8 - Asteroid

Hi @AkimasaKajitani 

Thank you for the solution but like I mentioned I have (few) 7 to 8 columns which have dates with the format  2020-12-31 for which I am using Multi Field formula because I have to adjust all 8 columns to format

31-12-2020 from 2020-12-31.

 

Can you help in that ? 

 

Thanks 

Areeba

AkimasaKajitani
17 - Castor
17 - Castor

Hi @areeba 

 

I have recognized that you want to change multiple fields into required field.

 

You can use Multi-Field Formula.

Firstly I recommend you change the field type to String by Select tool.

An then you have to use Multi-Field Formula at below setting.

 

AkimasaKajitani_0-1605888177799.png

 

DateTimeFormat(DateTimeParse([_CurrentField_],"%y-%m-%d"),"%d-%m-%Y")

 

AkimasaKajitani_1-1605888218275.png

 

 

Please refer attached file.

 

 

Labels