Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
ALTER.NEXT:

Join us on Dec 2 for a half-day virtual analytics + data science event!
US & CA customers only

SAVE YOUR SPOT
It's the most wonderful time of the year - Santalytics 2020 is here! This year, Santa's workshop needs the help of the Alteryx Community to help get back on track, so head over to the Group Hub for all the info to get started!
SOLVED

Date Format

Highlighted
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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@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 reboot. Order shall return.
Highlighted
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 ?

Highlighted
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

Highlighted
Alteryx Certified Partner

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!

Highlighted
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

Highlighted
Alteryx Certified Partner

Hi @areeba 

 

Yes, Null is ignored at this formula.

Sample is below.

 

Input:

AkimasaKajitani_0-1606008328018.png

Output:

AkimasaKajitani_1-1606008340381.png

 

Labels