community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

How to change single week number to 2 digits

Meteoroid

 Hi,

 

The data I'm trying to join has week number such as 201501, 201502, and so on. My other data source has separate year and week columns so I create a formula (ToNumber(ToString([Year],0) + ToString([Week],0))) to make it the same format as the other data source.

 

And here's my question:

 

Data Source 1 week number format:

201501, 201502~201509

Data Source 2 week number format (The one I combined year and week columns)

20151, 20152~20159

 

Is there any ways to make 1 digit week number to 2 digits week number so I can join them togehter?

 

Thanks,

Claire

 

ACE Emeritus
ACE Emeritus

In generating your new data from "DataSource2", try:

ToNumber(ToString([Year],0) + PadLeft(ToString([Week],0),2,"0"))

 

Highlighted
ACE Emeritus
ACE Emeritus

I don't know the name of your field so I called it [yearmonth] but try this..

 

if Length(trim([yearmonth field])) = 5 THEN LEFT([yearmonth],4) +'0' + RIGHT([yearmonth],1) ELSE [yearmonth] ENDIF

 

My syntax might be a litle off and if the field type is an integer then change the field to a STRING first

Assuming it is an integer called yearmonth then thnk:

iiF([YearMonth]<100000,floor(yearMonth/10)*100 + mod(yearmonth,10),[YearMonth])

will work

Meteoroid

Hi John,

 

It works! Thank you so much!

 

Have a great afternoon :)

 

Claire

Meteoroid

Thank you tsturge! Your method also works for me :)

Looking at @JohnJPS, I think a slightly simpler formula which should work is:

 

[Year] * 100 + [Week] 

 

ACE Emeritus
ACE Emeritus

LOL.... @jdunkerley79 ... but that's way too easy. :smileylol: 

Labels