Get the values from the nearest working day's values if the date is the weekend or holiday

c20091929

Hey guys,

First post here.

I was trying to process some StatsCan data.

I have built the logic that can download the data from the StatsCan website and write those data into our DB.

Currently, I need to solve one last issue before I can write those data into our DB.

This data is daily data which contained the values from Monday to Sunday. The values will be 0 during the weekend and holidays.

I am trying to create a new column called adjusted value and use the values from the nearest working day's values if the date is the weekend or holidays.

For example, 2021/01/30 was Saturday, I want to use the values from Frday 2021/01/29 in the new column.

Thank you so much for your help!

Workflow

Accepted answers

clmc9601

Hi @c20091929,

Sure, here you go! There are several ways to solve this, but I did so by adding a second multi-row formula tool that looks further behind and ahead than a single row. I think the adj_values that are still null are from before the stock existed.

If this helps, it would be great if you could "mark as solution" the post(s) that solved your question!

Populate weekend values from surrounding rows.yxmd

All comments

clmc9601

Hi @c20091929,

I have an idea for you! However, the file you sent does not include the .exe parts so I'll just write out the expression below and you will probably have to alter it for your data.

I would use a multi-row formula tool. Group by whatever fields will make each group populate with each row as a separate date. That might be grouping by [VECTOR] and [GEO] or [Financial market statistics] and [GEO]; I can't quite tell from your image.

Immediately before the multi-row formula tool, sort by those grouping fields and then by [REF_DATE] ascending.

In the expression editor, try an expression like this to populate your new column [Adj_Value]:

IF DateTimeFormat([REF_DATE],"%a") = "Sat" // if the current row is a Saturday,
THEN [Row-1:Value] // then use the previous row's value. Your setup will need to ensure that the previous row is a Friday
ELSEIF DateTimeFormat([REF_DATE], "%a") = "Sun" // if the current row is a Sunday,
THEN [Row+1:Value] // then use the next row's value. Your setup will need to ensure that the next row is a Monday
ELSE [Value]
ENDIF