Combination of math formula and rule engine

Good Day , dear KNIME users :blush:

I am struggling with combination of rule engine and math formula.
In this case I have to check date with the next row, if they are equal I have to take difference between lets say row3 Column D - row 2 Column E (with previous row)

I have kinda done it using rule engine and lag, but seems like it is not proper solution. I can use snippers, but unfortunately I do not know programming language yet :frowning_face:


date.knwf (80.1 KB)

I have attached my current workflow
Looking for you help and great ideas :smiling_face_with_three_hearts:

1 Like

Hello @Karlygash,

Lag node followed by Rule Engine seems like a way to go in this case. One things that could help you is a negative lag to get next date(s) one row up. See here:

Br,
Ivan

2 Likes

Yes I agree with @ipazin , @Karlygash, I’m not averse to using the odd java snippet, but really I only use them if:

  1. It would (really) simplify a flow, avoiding a large number of KNIME nodes
  2. I cannot think of a totally non-scripting/programming way to achieve the desired result, using the other nodes.

Yes you could use a java snippet in place of the lag columns here, and I’m always happy for people to learn some java or python, or both, so it might look something like this:

with the java snippet coded like this:

and if you really felt like it, could (probably) go the whole hog and replace all the nodes with the one snippet :thinking: :joy:, but I wouldn’t do that… and the reason is that the use of standard nodes such as Lag columns have a clear and defined purpose and have been previously tested so you can be confident that they will do the job asked of them. It is much easier and safer to put in a couple of Lag Column nodes than it is to hand craft a small piece of java each time. If you share workflows, you are also reliant on other people understanding the purpose of your code!

A java snippet on the other hand is very powerful, but every line of new code written increases the chance of errors creeping in. I wouldn’t want them to not be available though as they allow us to extend functionality without leaving KNIME if we really need to. But the emphasis I think should always be on “really need to”.

3 Likes

Hi @Karlygash

Hope you are doing well since last time.

I’m joining here a possible solution based on @ipazin suggestions (thanks @ipazin ;-)!)

20210623 Pikairos Combination of math formula and rule engine.knwf (117.7 KB)

I just realized that @takbb was posting at the same time an alternative solution. You now have the choice but please validates the one by @takbb, He deserves it :wink: ! Excellent solution and well documented answer, kudos !

Best wishes,

Ael

3 Likes

Hi @aworker , thanks but no, on this occasion I was writing one of my “life , universe and everything” essays, so wasn’t really providing a solution, so this one is all yours! :wink:

2 Likes

@takbb you made me laugh haha ha ! We are not going to quarrel about it lol :laughing: ! So funny :rofl: !

@Karlygash has the last word lol :wink: !

Best wishes to you all !

Ael

2 Likes

@takbb @ipazin @aworker thank you for you help :smiling_face_with_three_hearts: and I really appreciate your effort
I would love to use java snippers , but my skills are not enough yet((
@takbb your solution is really great, just one node and proper, cool solution!! I agree with that when you are sharing your workflow other users might not understand it. Specially when your customer asks to load input files by themselves.
@aworker as usually you always give the best solution using KNIME nodes. Actually I haven’t used Rule Based Row Splitter yet(. It is good chance to practice using this node

1 Like

Hi @Karlygash

Thanks for your compliments :relaxed: and glad you find these two solutions useful :wink: :+1: !

I totally agree with the pros and cons of KNIME coding versus classic coding. Having said that, it’s really amazing how sometimes, a single line of classic programming (Java, Python or RegEx) can replace a good dozen of KNIME nodes lol ! @takbb (with Java or Python) and @armingrudd (with RegEx) give us very often the opportunity to learn more, and thus save us from racking our brains the whole day haha ​​ha :crazy_face: ! Kudos again to them !

Have a lovely day :smiling_face_with_three_hearts: !

Ael

3 Likes

Thanks, you too!
Are there any courses ?))related to knime snippers?

1 Like

There is no much difference between the Java/Python/JavaScript used in the KNIME snippets compared to the one used in other environments, so classic tutorials on Java/Python/etc. from the web would be useful to start with. The only thing different you need to know is how to gather the data and how to return the results.

Most often I learn this by looking at simple examples in the KNIME forum or in the hub. One thing I really love about the KNIME web site is that is full of examples associated to every node. For instance, if you need to know how to program in Python with the Python Script node, good places to look at are the following:

You’ll find there explanations and a list of workflows as examples:

or

Be aware that some of the scripting nodes come already along with examples called templates, to illustrate how the data need be gathered and returned:

These are just some hints to start with.

Best wishes,

Ael

5 Likes

Thanks for your links that you provided, I started with Java, but it was a bit tough for me(( Then I moved to python) Hope that in future I can write some extra functions as you using snipper)
Thanks a lot for your help :smiling_face_with_three_hearts: :sparkling_heart:

1 Like

@aworker, hello=)
I am using your solution right now, I have one question related to moving aggregation.
Is it possible to move the row within the group?

For example here in this case I have only one employee, if I add other employees, it moves down appropriately but it is taking incorrectly

Hi @Karlygash and welcome back :slight_smile: !

I guess what you mean is that this should be done separately (independently) based on employee, so the rows for one employee should not be taken into account for the next or the previous employee :thinking: ? Is that the right interpretation ?

Looking forward to helping

Ael

1 Like

@aworker , yeah exactly) :grimacing:
sorry for my poor english)

I have in case uploaded workflow, but the most them are done by you and @takbb :grimacing: :grimacing: :grimacing:
emp_data.knwf (536.9 KB)

Do we have a possibility in knime like window functions in sql?

No problem at all @Karlygash (and your english is perfect :heart_eyes:)

I have downloaded the workflow and I’ll let you know when the solution is implemented.

I’ll be back soon. Hold the line please :laughing: !

Best

Ael

3 Likes

@aworker thanks :green_heart:
Yeah, I m holding the line:D

Hi @takbb

I have just downloaded the last version of the workflow by @Karlygash and while using the last download version of KNIME (4.3.3), I’m facing this problem:

image

and I do not know why. I guess this is working on your side. If so, would you mind to take over from here and reply to @Karlygash question :thinking: ? I believe a solution may be a “group loop” node by employee so the processing is done only based on separate employees. But may be you have a better solution in mind :wink: !

Sorry @Karlygash and @takbb for not been of help here :cry: !

Best

Ael

1 Like

@aworker , oh yes, here are some updates. @takbb implemented new cool components, I installed them and that s why it s asking about updates
Component name is First and Last for Group
image
I do not know how to share with comments:D

Just added link =)

hi @aworker and @Karlygash

yes the workflow contains three copies of a component but clicking Yes to update (or No :wink: ) should allow it to work. However, what I’ve done now is “disconnected” the component from its reference on the hub so hopefully it won’t ask for updates any more.

Apologies for delay, but I had to catch up on the previous messages as I hadn’t been following the “moving aggregation” part of the flow, as I knew that bit was already in (more) capable hands. :thinking: :smile:

However, I think I understad, that basically each time the employee name changes, we need to have the “last” values from the moving aggregation set to missing just as it is for the final row in the dataset.

Assuming I got that bit right, then that “First and Last for Group” component can come to the “rescue”. If we ask it to mark the first and last row for “Employee Name”

image
then what it does is populate two columns “EmployeeStart” and “EmployeeEnd”.

This then makes it super easy to use a Rule Engine to “fix” the “last” values at the end of each Employee window

image

image
image

Is that what is needed? I know it’s a bit of a “cheat” but this is exactly the kind of situation that component was written for :wink:

(And apologies if I’ve misunderstood what was being asked!)

emp_data_2.knwf (619.3 KB)
[Edit: re-uploaded without resetting data :wink: ]

2 Likes