Adding new rows based on certain condition

[sreekanthac]

Hi there,

I have a table with hotel booking information. In the database, each row shows the information about the hotel name, the Start Date and the End Date. I need to add a row for each date between the Start Date and End Date as shown in the below example. How can get it done? Thanks in advance for your support.

Regards,
Sreekanth

[lelloba]

Hello @sreekanthac ,

have a look at this:

Adding new rows based on certain condition – lelloba

Have a nice day,
Raffaello
Linkedin

[sreekanthac]

Wow, this is quite complicated than I expected. I have experience with Alteryx where we can use one tool (node) called Generate Rows. I believe KNIME should also introduce a single node to make it much easier. This is a very common situation I confront with where I need to generate rows based on the upper and lower limits. Thanks @lelloba for your workflow. I will wait for some more time if I could find an easier option before I mark your’s as the solution.

[lelloba]

Have a look at the link above, I have created an alternative with less knodes

Raffaello

[gonhaddock]

Hello @sreekanthac and welcome to the KNIME forum

@sreekanthac

I have experience with Alteryx where we can use one tool (node) called Generate Rows. I believe KNIME should also introduce a single node to make it much easier.

One Row to Many

This is an example on how to implement it:
Adding new rows based on certain condition.knwf (114.2 KB)

BR

[DiaAzul]

@sreekanthac

Hi, what you have requested doesn’t have to be complicated in KNIME. The following workflow achieves what you are looking for with very few nodes. The advantage is that this approach creates a new column containing a date for each day in the range of dates between check-in and check-out.

• The table creator node contains the sample data. The only difference with your data is that I used category rather than hotel name (sorry, this is habit when creating examples ).
• The variable loop start takes each row and concerts the columns to values per row.
• The start and end date are used as flow variables to create a range of dates between start and end, with a duration of one day between samples.
• The category (hotel) flow variable is appended as a column (category).
• End loop iterates over all the rows
• Inner Join on both tables expands your initial table with the newly created date ranges.

It’s not one node, but provides flexibility if you want to change from days to other units (e.g. hours, weeks, months, etc.)
Date Expansion.knwf (17.5 KB)

DiaAzul
LinkedIn | Medium | GitHub

[gonhaddock]

Hi @sreekanthac
Sorry, I was in hurry and attached a wrong file (@lelloba 's one), this was the intended workflow:

20230214_adding_rows_conditional.knwf (37.8 KB)

BR

[sreekanthac]

@gonhaddock yes, I got confused with the inital workflow you attached. I liked your approach for the fact that you are avoiding loops. I am not sure how well KNIME handles loops. Generally, it is said that using loops would affect the performance. I am a newbie in KNIME, started using it a couple of days ago. But I have experience with Alteryx and trying to use that knowledge to learn KNIME.

[iCFO]

It takes a little while to get your feet under you in KNIME coming from Alteryx, but once you do you will never go back!

KNIME’s integrations and open source approach allow equate to a much more universally capable platform. The heavier use of “flow variables” allow us to automate most node settings and create much more dynamic solutions. The extremely active community sharing opens up a ton of potential with the creation and open sharing of thousands of custom developed nodes, components and workflows. User interactions can also be highly customized (almost to the point of feeling like an app) by creating components that are managed via their interactive views.

I still have active licenses but I haven’t opened an Alteryx program on a computer in a year and a half, and I hope that I never have to again…

[sreekanthac]

I agree with you @iCFO . In a short period, I already got a good feeling of using this tool. I will definitely continue using this and gain more insight.

[sreekanthac]

Thank you all for your support. Though @lelloba and @DiaAzul provided some great workflows, I would mark the one from @gonhaddock as my solution. I need to spend some more time with KNIME to understand some of the nodes you have used.