Getting the date right after Union

[aish28warya]

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/period	Required data
2020	A
2020	C
2020	D
2/1/2020	AA
2/1/2020	EE

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

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

Any help will be much appreciated. Thanks in advance.

[echuong1]

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.

[apathetichell]

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]

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/period	Required data
2020	A
2020	C
2020	D
3/1/2020	AA
3/1/2020	EE

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 · Accepted solution]

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

[echuong1 · Accepted solution]

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