Appending new data and updating the date from above

[sbarnor]

Hello,

I am currently creating and appending information in a consolidated headcount report. I am appending the new information after the last row of information. One of my columns is a date column which has year to dates in this format (Jan 24, Feb 24 etc.) I want a node or calculation which looks at the last row of data in the date column I.e. Jun 24 update to July 24.

[rfeigel]

It’s not completely clear what you’re trying to do. If you want to extract the last row, try the Top k Row Filter node. Sort on the RowID (assuming they’re in descending order.)

[sbarnor]

Appending to the last row is fine, it works, but in my new data set, on the very left hand side, column A, it should have a new date I.e July 24 and then next month when I do this exercise again, it should have August 24. This has been updated manually in the past and I want that to be included in the flow.

[MartinDDDD]

I think I get what you are after:

1. your existing data has a history with dates for each month
2. based on that data you want to calculate the date for the next month that reporting is required and insert that record

If that is what you are after this may help you with an implementation - I created some dummy data.

In an nutshell you filter the existing data to only keep the most recent date, then shift it forward one month, then add it as a column to the “new record” and then concatenate old with new.

Prototype:
continuousDate.knwf (84.5 KB)

Overview:

[sbarnor]

Yeah that is exactly what I need continuous dates in my update file. Apologies for the confusion I am new working with KNIME, I will try this and let you know how it goes. Much appreciate it the flow and the diagram.

[sbarnor]

I had a question what if the existing data is in an excel file which the new data populate later? My flow currently is prepping the July data from a different source before updating my final file? Your flow looks like it’s taking a new data and an existing data and concatenating them together. I’m just thinking of how to bring the final file into the flow to filter for the current date.

[MartinDDDD]

Is it possible for you to share an anonymised example with source file, format of new data before / after? That’d help to take the guessing / interpretation away

[sbarnor]

July 2024 Workday Headcount Report.xlsx (467.5 KB)
this the file which the information will be extract from each month

Italy HC Analysis - June 2024 - Copy.xlsx (516.9 KB)
This the file which will be updated using the KNIME flow for example in July

This is my current flow without the changes for the data tip you gave me earlier

Let me know if you need any more information

thanks,

[sbarnor]

Apologies it looks like I didn’t reply back to you but to the post I did. Are you able to retrieve the information or should I sent it to you directly.

[sbarnor]

July 2024 Workday Headcount Report.xlsx (467.5 KB)

this is a similar which the information I will be extract from each month and it normal has every employee in the company but as you can see from my flow I am only filtering the MRC “2330” which relates to my area

Italy HC Analysis - June 2024 - Copy.xlsx (516.9 KB)

I am updating this file above as my consolidation Headcount file

[MartinDDDD]

Hey,

thanks for the additional data. The general approach to extract the latest date from the existing list and calculating the next month remains the same.

Based on your data the way to get there is different as you have Month name and year separated by a space in your period column. That makes things slightly more complicated as you cannot just convert that to a date and moreover the format seems inconsistent (sometimes three letters, for June then it is 4 letters…)

I’ve added the start of a second solution where:

1. a lookup table is manually generated that maps Month name as it appears in the table to a numeric period (you will need to adjust so that each month appears as it is in your source)
2. the Period column is split by " " to separate month name and year
3. for each row the numeric period is looked up from the manually generated table in 1)
4. using groupby to get maximum value in the numeric month and year column
5. calculate next month / year in column expressions
6. lookup month name in lookup table
7. turn the table that now contains the name of the next month and the year into variables
8. pass those variables to column expressions that is connected to your new data and add a column “Period” based on new month name and year

After that you’ll have to work on aligning the table with the new data to match the format/structure of your source data and then concatenate them.

Prototype:
continuousDate.knwf (1.0 MB)

Overview:

[sbarnor]

Hey,

Thank you for the update of the flow and apologies about the date format. That was me when creating a dummy one for this exercise. The date format will be three letters and then a space but also flexible to reduce the space if it makes it easier within the flow. I will work on this and will post updates when I have done thank you very much this is such a big help.

[sbarnor]

KNIME_Headcount 1.knwf (36.4 KB)

This is my current flow to also illustrate my current issue