Compare data over months

KNIME Analytics Platform
1

Could you help me with the right workflow for the below question?
I need to compare employee data month on month to find if any employee has changed his functional area or position . Overall across the year 2018 I need to figure out how many employees have cross moved across location/position/functional area.
Capture
I started as below , but comparing more than 2 parameters month on month looks tedious. Is there any other logic?

2

Hi poojaph,

Please could you post the structure of your input data (i.e., the data you are importing via the Excel Readers). Does each Excel reader represent the employee records for 1 month? What is the granularity of the data and the format of the information?

No need to post confidential employee information - just the table structure of the inputs is fine.

1 Like
3

Hi,
Data dup .xlsx (23.1 KB)

Have attached the excel table format. and it remains same across months.

4

Look if this node can help you.


If you use a Set as aggregation mode then you can compare the set columns as 1 field. That means that is no changes happened to any field set fields will be equal.

5

Thanks for attaching your spreadsheet.

In general, it’s undesirable to import the same structures more than once (this is duplicated effort).

The workflow pictured should solve your issue. It reads the tabs in your spreadsheet and then iterates through each one, importing one sheet at a time.

The second part of the workflow takes the list of employees for 1 month and compares it to the list in the next month.

This is best way I can think of doing this - but there could be better solutions.

image
image1562×328 32 KB
Compare data over months.knwf (59.9 KB)

6

Thank you, will try that.

7

Hi Thank you for giving me this workflow. I was trying to do the same but during string manipulation I have encountered a problem.
The rearranged sheet has format 2018-Dec-01,but few months it has picked it differently eg:il20-Apr-01. I tried to redo the string manipulation expression but it did not work. Can you help me?

8

Hi poojaph

I think this must be because the tabs in your excel file are named inconsistently. Unless there are tons of tabs (>20), it may be quicker for you just to rename your tabs consistently so that the string manipulation works on all of them.

1 Like