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
areeba
8 - Asteroid

Hi @AkimasaKajitani 

@This will help a lot , just the last thing.. is there also a way not to include 0 before day and month

for eg:  in the above solution Hiring date is appearing as 03-08-2020 but I want to print it as ‘3-8-2020’ only like without 0 before day and month. 
Is it possible?

 

Thanks

Areeba

MarqueeCrew
20 - Arcturus
20 - Arcturus

@areeba ,

 

The final formatting is possible, even though it doesn't appear to be directly supported by the date specifiers.  Visually, you can explain this with your statement "like without the 0 before day and month".  If you can change that to TrimLeft(day & month), "0") then the formula would be:

 

TrimLeft(DateTimeFormat([_CurrentField_],"%d/"),"0") +
TrimLeft(DateTimeFormat([_CurrentField_],"%m/%Y"),"0")

 

This provides you with the final mile of the challenge.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
areeba
8 - Asteroid

Hi @AkimasaKajitani 

 

I changed the data type from Date to string and applied the settings of Multi field formula , it worked 😊 but only thing is inspite of showing right results it is throwing error "Type Mismatch. Number provided where a string is required."

 should I ignore it ?

areeba
8 - Asteroid

Hi @MarqueeCrew 

 

I tried applying this formula but unfortunately it did not work ☹️. Is it  a another way we can remove 0 after applying multi field formula which @AkimasaKajitani  suggested.

Please note my column has string datatype now.

 

Thanks

Areeba

AkimasaKajitani
17 - Castor
17 - Castor

Hi @areeba 

 

Are there null or numeric data?

If the function work well, you can ignore the message.

 

And I reply for last question.

 

This is the way of the unnecessary Zero to erase. 

TrimLeft(REGEX_Replace([_CurrentField_], "-0", "-"),"0")

Only Trim function is not meet, but the RegEx function is very helpful.

 

AkimasaKajitani_0-1605929943568.png

 

Off course, you can merge the previous function

 

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

 

Oh, very complex!

areeba
8 - Asteroid

Hi @AkimasaKajitani 

 

Bingo ! it worked .You Rock 😇

The answer to your question is that yes there are NULL in that field which I am removing them with Data Cleansing tool as it is of String datatype.

Can those NULL be remove with any addition in this formula?

 

Regards

Areeba

AkimasaKajitani
17 - Castor
17 - Castor

Hi @areeba 

 

Yes, Null is ignored at this formula.

Sample is below.

 

Input:

AkimasaKajitani_0-1606008328018.png

Output:

AkimasaKajitani_1-1606008340381.png

 

Labels