Alteryx Designer Desktop Discussions

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

Getting the date right after Union

aish28warya
6 - Meteoroid

Hi all,

 

I've a situation here. I've union two different data sets for the month of February. Below is what I see after Union.

 

posting year/periodRequired data
2020A
2020C
2020D
2/1/2020AA
2/1/2020EE

 

I want to convert 2020 to the correct date of 2/1/2020. So that it can display

 

posting year/periodRequired data
2/1/2020A
2/1/2020C
2/1/2020D
2/1/2020AA
2/1/2020EE

 

Any help will be much appreciated. Thanks in advance.

5 REPLIES 5
echuong1
Alteryx Alumni (Retired)

Should it always be Feb 1 if it's only the year? If so, you can use a conditional statement to add the missing pieces.

 

I added a datetime to convert it to a proper date format.

 

echuong1_0-1619026501499.png

 

apathetichell
18 - Pollux

Is everything going to February or is there another column with a  month identifier? Assuming it's the former - you can do something like if length([posting year/period])=4 then "2/1/2020" else [posting year/period] endif

 

 note -to get this into a date you'll have to add a new column or go through a multi-field formula with a datetimeparse([field],"%d%m%Y") or something like that.

aish28warya
6 - Meteoroid

I can understand the dilemma here. The files are set up in such a way. If I'm doing for the month of march, below is what I see.

 

The correct date for that particular month comes from second data source, to which I want to convert my first data source date.

 

posting year/periodRequired data
2020A
2020C
2020D
3/1/2020AA
3/1/2020EE

 

In the first data source, it only reflects year in the posting year/period column. So if the year changes, then only that value changes.

Luke_C
17 - Castor

Hi @aish28warya 

 

 

If you're only processing one month at a time, the simplest way might just be to use a summarize tool to get the max value of the first column (will always be the full date), and append that back to all the records. This avoids the complexity of datetime functions

 

Luke_C_0-1619027287886.png

 

 

echuong1
Alteryx Alumni (Retired)

Try this - it takes the day and month from the maximum in the dataset, and adds the original year.

 

echuong1_0-1619027403533.png

 

Labels