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 data2020A2020C2020D2/1/2020AA2/1/2020EE I want to convert 2020 to the correct date of 2/1/2020. So that it can display posting year/periodRequired data2/1/2020A2/1/2020C2/1/2020D2/1/2020AA2/1/2020EE Any help will be much appreciated. Thanks in advance.

Getting the date right after Union

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.

Workflow

Datasets

Input

Reporting

Connectors

Date Time

Best Practices

Transformation

Preparation

Time Series

Accepted answers

Luke_C

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

New Workflow21.yxmd

echuong1

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

New Workflow1.yxmd

All comments

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.

New Workflow1.yxmd

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.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335