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!

[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

 I hope this makes sense and helps!

[c20091929]

Hi friend, 

Thank you so much for your reply, and I hope you had a great day. 

I have reuploaded the sample data file and fixed the designer file. 

I am not sure you can give me a quick sample?

Still trying to learn, haha...

So should I include a calendar function that identifies the date as the weekend?

[Qiu]

@c20091929 
I used two multiple Row tools.

[clmc9601]

@c20091929,

Here you go!

 

[c20091929]

THANK YOU SO MUCH!!!!!

It works well and solved my issue!!!!!

Got one more question for you.

We don't have an issue for the weekend now but we have the issue for the holidays. 

Please find below:

All the yellows are the weekends. 

2/15 was family day in Canada and 2/14 should pull data from 2/15....

Is there a way to pull the previous or next day's data if the value = 0?

so not only the weekend. 

[clmc9601 · Accepted solution]

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!

[c20091929]

haha!!

Thank you so much for your help!

I was thinking the same thing and added the second and third layers. 

But it solved the issue!! Thank you again!