Appending Data

[suby]

Hi,

I have a monthly snapshot of Data starting from 30/11/2022 to 31/03/2023.(5 Months of Data combined)
as a single .hyper file.

Also i have a workflow which runs daily and produces daily output ( Daily Snapshot).

My requirement is to to take the .hyper file ( monthly snapshot) into my daily workflow and then append the Daily
snapshot data everyday.

so when i run my workflow today it should have the Dates like this (with monthly and Daily snapshot appended)

30/11/2022
31/12/2022
31/01/2023
28/02/2023
31/03/2023
05/04/2023

so when I run the workflow tomorrow it should have the Dates like this
(with monthly and Daily snapshot appended by overwriting the previous Day snapshot)

30/11/2022
31/12/2022
31/01/2023
28/02/2023
31/03/2023
06/04/2023

and when i run the WF on the 1st of May 2023 the Monthly snapshot of 30-11-2022 should be removed
and appended with 31-04-2023.

The idea is to combine monthly and Daily snapshot of one single .hyper file and the daily run should
overwrite the previous day and when it comes to first of every month it should remove the first month in
the hyper file and add the new month end one.

Any Ideas or thoughts around this please attached a sample .yxzp file for reference.

[Yoshiro_Fujimori]

Hi @suby 

I understand that you want to remove rows with [DATA DATE] which are

- older than 5 months

- in the same month but not today.

Then I would first add everything and then filter the above rows out.

Workflow

Formula Tool

fiveMonthsAgo = 

DateTimeAdd([Today],-5,"month")

withinFiveMonths = 

DateTimeDiff([DATA DATE],[fiveMonthsAgo],"days") < 0

isSameMonthOfToday = 

DateTimeMonth([DATA DATE]) = datetimemonth(DateTimeToday()) 

AND

[DATA DATE] != [Today]

Output

I hope it works for your case.

[PangHC]

1. generate row tool for date
   • initial:       datetimetoday()
   • condition: datetimediff(datetimetoday(),[Date],"month")<5
   • loop:         datetimetrim(datetimeadd([Date],-1,"month"),"lastofmonth")
2. formula tool to match the filename
3. join tool to filter the right file
4. continue your flow.