Icon

20230423 Pikairos JustKNIMEIt Season 2 Challenge 4 Spread Yearly Values Across Months Without Loop

Description: Your team created a summary of the total amount spent last year on each project. The data looks like the following:

Year Area Project exp
2022 area A proj A 120
2022 area B proj X 160

The finance department of your company, however, would like to have a more granular overview of the expenditures. In particular, the expenditures for projects in area A should be spread equally across 12 months, while those for projects in area B should be grouped by quarters. In the end, the above table should look like the following:

Year Area Project exp
2022 area A proj A jan 10
2022 area A proj A feb 10
...
2022 area B proj X Q1 40
2022 area B proj X Q2 40


Challenge 04: Spread Yearly Values Across Months Your team created a summary of the total amount spent last year on each project. The data looks like the following:Year Area Project exp2022 area A proj A 1202022 area B proj X 160The finance department of your company, however, would like to have a more granular overview of the expenditures.In particular, the expenditures for projects in area A should be spread equally across 12 months, while those forprojects in area B should be grouped by quarters. In the end, the above table should look like the following:Year Area Project exp2022 area A proj A jan 102022 area A proj A feb 10...2022 area B proj X Q1 402022 area B proj X Q2 40 2022 ProjectsCreate Number of RowsColumnarea A = 12 rowsarea B = 4 rowsGenerateDuplicate RowsBased on theNumber of RowsColumnCreate RowNumber ColumnRe-sortColumnsViewTableSplit Rows byAreatop = Area Abottom = Area Bexp / number of rowsRank by Rowand Group byArea and Projectto Assign ConsecutiveRow Numberingper Area/ProjectConvert Datefrom Stringto Date FormatCreate a Date inString Formatfor the FirstDate of Each MonthYYYY - MM - 01Extract MonthAs Name fromthe DateJoin "Q"to the Rank Numberto Create Quarterse.g. Q1RenameMonth to PeriodFilter OutUnwantedColumnsCreate 3Letter Versionof Month Table Reader Rule Engine One Row to Many Math Formula Column Resorter InteractiveTable (local) Row Splitter Math Formula Rank String to Date&Time String Manipulation Extract Date&TimeFields String Manipulation Concatenate Column Rename Column Filter String Manipulation Challenge 04: Spread Yearly Values Across Months Your team created a summary of the total amount spent last year on each project. The data looks like the following:Year Area Project exp2022 area A proj A 1202022 area B proj X 160The finance department of your company, however, would like to have a more granular overview of the expenditures.In particular, the expenditures for projects in area A should be spread equally across 12 months, while those forprojects in area B should be grouped by quarters. In the end, the above table should look like the following:Year Area Project exp2022 area A proj A jan 102022 area A proj A feb 10...2022 area B proj X Q1 402022 area B proj X Q2 40 2022 ProjectsCreate Number of RowsColumnarea A = 12 rowsarea B = 4 rowsGenerateDuplicate RowsBased on theNumber of RowsColumnCreate RowNumber ColumnRe-sortColumnsViewTableSplit Rows byAreatop = Area Abottom = Area Bexp / number of rowsRank by Rowand Group byArea and Projectto Assign ConsecutiveRow Numberingper Area/ProjectConvert Datefrom Stringto Date FormatCreate a Date inString Formatfor the FirstDate of Each MonthYYYY - MM - 01Extract MonthAs Name fromthe DateJoin "Q"to the Rank Numberto Create Quarterse.g. Q1RenameMonth to PeriodFilter OutUnwantedColumnsCreate 3Letter Versionof Month Table Reader Rule Engine One Row to Many Math Formula Column Resorter InteractiveTable (local) Row Splitter Math Formula Rank String to Date&Time String Manipulation Extract Date&TimeFields String Manipulation Concatenate Column Rename Column Filter String Manipulation

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: LAMBERT
Created at: 2023-04-21
On NodePit since: 2023-04-27
Last update: 2024-04-27
Created with KNIME version: v4.7.2
Tags: JKISeason2-4One Row to ManyRule EngineMath FormulaRankString Manipulation

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!