Multiple Date Columns from one date column till specific years.

kiotsuresh · 2024-03-26T09:11:12+00:00

Hi, I have A requirement like below. Input: StockBuy/SellStock DateStart DateEnd DatePriceAmazonBuy12/31/202311/29/20233/1/202410AppleBuy12/31/202312/27/20235/1/202420GEBuy12/31/202312/27/20235/1/202430MicrosoftBuy12/31/202311/24/20235/1/202440Applied MaterialsBuy12/31/202311/24/20235/1/202450 Output: Date Column output :…

kiotsuresh

Hi,

I have A requirement like below.

Input:

Stock	Buy/Sell	Stock Date	Start Date	End Date	Price
Amazon	Buy	12/31/2023	11/29/2023	3/1/2024	10
Apple	Buy	12/31/2023	12/27/2023	5/1/2024	20
GE	Buy	12/31/2023	12/27/2023	5/1/2024	30
Microsoft	Buy	12/31/2023	11/24/2023	5/1/2024	40
Applied Materials	Buy	12/31/2023	11/24/2023	5/1/2024	50

Output:

Date Column output : The Column after Price needs to be derived from Stock Date and the other columns needs to be created for upcoming months for next 4 years.

Value output: the values for the new date columns needs to be created by comparing the column header date with start date and end date and price

"if start date<column header date and end date >column header date then Price else 0 end"

. Is this possible to achieve in Alteryx

Stock

Buy/Sell

Stock Date

Start Date

End Date

Price

12/31/2023

1/31/2024

2/29/2024

3/31/2024

4/30/2024

5/31/2024

6/30/2024

Amazon

Buy

12/31/2023

11/29/2023

3/1/2024

Apple

Buy

12/31/2023

12/27/2023

5/1/2024

Buy

12/31/2023

12/27/2023

5/1/2024

Microsoft

Buy

12/31/2023

11/24/2023

5/1/2024

Applied Materials

Buy

12/31/2023

11/24/2023

5/1/2024

Datasets

Input

Custom Tools

Connectors

Common Use Cases

Transformation

Dynamic Processing

Join

Interface Tools

Data Investigation

Accepted answers

danilang

Hi @kiotsuresh

Here's a dynamic version.

It uses @aatalai's basic framework, but then uses a RecordId to as the Column Header field in the Crosstab to get the proper column order and then a Dynamic Rename to get to correct date format in the headers

Dan

Multiple Date Columns(dynamic).yxmd

binu_acs

@kiotsuresh one way of doing this

MultipleDate_26032024.yxmd

All comments

aatalai

@kiotsuresh this should help, let me know how you get on

Multiple Date Columns from one date column till specific years.yxmd

kiotsuresh

Will this work if the incoming fields are in Date Data Type?

aatalai

should do, but if not use a select tool/formula tool to change data types

kiotsuresh

if you run your workflow for Dec 2023&Jan 2024 we see the correct days, 2023_12_31&2024_01_31 for rest of the months the days are populated as 29 and 28 which is incorrect, which should be the total number of days in the month. Also Is it feasible to have the new columns like 31-Dec-2023 , 31-Jan-2024 instead of 2023_12_31,2024_01_31, if we can achieve this that will be the solution. I can do this using a select tool but i need it to be dynamic. please help

kiotsuresh

Thanks Dan, Wrt the no of days in every month, your workflow also produces an output where except Dec 2023&Jan 2024 rest all the month days is incorrect for ex: 29-Feb-2024 is right where 29-Mar-2024 is incorrect ( it should be 31-Mar-2024), Is there a way to fix this?

danilang

Hi @kiotsuresh

This is related to a logic error in the Generate rows tool. The original increment formula was

DateTimeAdd([Stock Date],1,"month")

This add 1 month to the date by incrementing the month number and then adjusting for shorter months. So 2024-01-31 + one month is 2024-02-31, but then adjusted to 2024-02-29 since Feb only had 29 days. Adding one month to 2024-02-29 gives 2024-03-29 which is valid, so the day never increases beyond 29 in any subsequent month

To get around this, change the Loop Expression to

ToDate(DatetimeTrim(DateTimeAdd([Stock Date],1,"month"),"lastofmonth"))

This pushes the new date to last day of the current month and then converts to a date

Dan

binu_acs

@kiotsuresh one way of doing this

MultipleDate_26032024.yxmd

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335